clear all
capture log close
set more off, perm
set matsize 1000
******************************************************************************************************************************************
*******Setting cd*******************
local DIR= "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\Data\Afrobarometer"
*local DIR = "C:\Users\lkaplan\Dropbox\Geocoded Aid and Conflict\Data\Afrobarometer"
*local DIR = "C:\Users\gehring\Dropbox\Geocoded Aid and Conflict\Data\Afrobarometer"
*local DIR = "Y:\entwicklungspolitik\Hiwi\A_Kai\Geocoded Aid and Conflict\Data\Afrobarometer"
cap cd "`DIR'"

***************************************ROUND 1***************************************
import delim "afrobarometer\afb_r1_adm1_intersect.txt", clear delim(";")
quietly{

gen round1 = 1
gen year = ""
* Set survey year
replace year = "1999" if country == "BOT"
replace year = "1999" if country == "GHA"
replace year = "2000" if country == "LES"
replace year = "1999" if country == "MLW"
replace year = "2001" if country == "MLI"
replace year = "1999" if country == "NAM"
replace year = "2000" if country == "NIG" /*Nigeria was conducted in 2001 and 2000 I aproximated it for 2000 here*/
replace year = "2000" if country == "SAF"
replace year = "1999" if country == "TAN"
replace year = "2000" if country == "UGA"
replace year = "1999" if country == "ZAM"
replace year = "1999" if country == "ZIM"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep geoname__1 area country language pfenow pfepas pfegrp pfefut povfoo povwat povhth povelc povinc rejone rejmil rejdic dmpext dmpsat trspre trspol trsnec trsarm trscts pfrfai pidwin pidcls iso name_0 name_1 year ID_adm1 ID_adm1_yr pariss pfrele supdem parctg
ren area region
* Unify question names (create missings if this question was not asked in the respective round)
gen ea_svc_a = .
gen ea_svc_b = .
gen ea_svc_c = .
gen ea_svc_d = .
gen ea_fac_a = .
gen ea_fac_b = .
gen ea_fac_c = .
gen ea_fac_d = .
gen ea_fac_e = .
gen ea_fac_f = .
gen ea_fac_g = .
gen ea_sec_a = .
gen ea_sec_b = .
gen ea_sec_c = .
gen ea_sec_d = .
gen ea_sec_e = .
gen ea_road_a = .
gen ea_road_b = .
ren language q2
ren pfenow q4a
gen q4b = .
ren pfegrp q5
ren pfepas q6
ren pfefut q7
gen q7b = .
ren povfoo q8a
ren povwat q8b
ren povhth q8c
ren povelc q8d
ren povinc q8f
gen q27a = .
gen q27d = .
gen q27b = .
gen q27e = .
ren rejone q28a
ren rejmil q28b
ren rejdic q28c
ren dmpext q40
ren dmpsat q41
gen q42a = .
gen q42b = .
ren trspre q52a
ren trsnec q52c
ren trspol q52b
gen q52d = .
gen q52e = .
gen q52f = .
gen q52g = .
gen q52h = .
ren trsarm q52i
gen q52k = .
ren trscts q52j
gen q52l = .
gen q81a = .
gen q81b = .
gen q81c = .
ren pfrfai q88a
gen q88b = . 
gen q89a = .
gen q89b = .
ren pidcls q90a
gen q89e = .
ren pid q90b
gen q102 = .

gen q10a_x = . 
gen q11a_x = .
gen q11b_x = .
gen q13_x = .
gen q15a_x = .
gen q15b_x = .
gen q15c_x = .
gen q16_x = .
gen q20b_x = pariss
gen q22_x = pfrele
gen q30_x = supdem
gen q24c_x = parctg
gen q49_x = .
gen q51a_x = .


gen round_afb = 1
gen ISO = ""
* Unify names of geographic units over rounds
replace ISO = "BWA" if country == "BOT"
replace ISO = "GHA" if country == "GHA"
replace ISO = "LSO" if country == "LES"
replace ISO = "MWI" if country == "MLW"
replace ISO = "MLI" if country == "MLI"
replace ISO = "NAM" if country == "NAM"
replace ISO = "NGA" if country == "NIG"
replace ISO = "ZAF" if country == "SAF"
replace ISO = "TZA" if country == "TAN"
replace ISO = "UGA" if country == "UGA"
replace ISO = "ZMB" if country == "ZAM"
replace ISO = "ZWE" if country == "ZIM"
gen country2 = ""
replace country2 = "Botswana" if country == "BOT"
replace country2 = "Ghana" if country == "GHA"
replace country2 = "Lesotho" if country == "LES"
replace country2 = "Malawi" if country == "MLW"
replace country2 = "Mali" if country == "MLI"
replace country2 = "Namibia" if country == "NAM"
replace country2 = "Nigeria" if country == "NIG"
replace country2 = "South Africa" if country == "SAF"
replace country2 = "Tanzania" if country == "TAN"
replace country2 = "Uganda" if country == "UGA"
replace country2 = "Zambia" if country == "ZAM"
replace country2 = "Zimbabwe" if country == "ZIM"
gen region2 = ""
replace region2 = "Erongo" if region == 1
replace region2 = "Hardap" if region == 2
replace region2 = "Karas" if region == 3
replace region2 = "Khomas" if region == 4
replace region2 = "Kunene" if region == 5
replace region2 = "Ohangwena" if region == 6
replace region2 = "Okavango" if region == 7
replace region2 = "Omaheke" if region == 8
replace region2 = "Omusati" if region == 9
replace region2 = "Oshana" if region == 10
replace region2 = "Oshikoto" if region == 11
replace region2 = "Otjozondjupa" if region == 12
replace region2 = "Caprivi" if region == 13
replace region2 = "Bulawayo" if region == 14
replace region2 = "Manicaland" if region == 15
replace region2 = "Midland" if region == 16
replace region2 = "Mashonaland West" if region == 17
replace region2 = "Mashonaland Cenral" if region == 18
replace region2 = "Mashonaland East" if region == 19
replace region2 = "Matabeland South" if region == 20
replace region2 = "Matabeland North" if region == 21
replace region2 = "Masvingo" if region == 22
replace region2 = "Harare" if region == 23
replace region2 = "Central Province" if region == 24
replace region2 = "Copperbelt" if region == 25
replace region2 = "Eastern Province" if region == 26
replace region2 = "North-Western Province" if region == 27
replace region2 = "Southern Province" if region == 28
replace region2 = "Lusaka" if region == 29
replace region2 = "Northern Province" if region == 30
replace region2 = "Western Province" if region == 31
replace region2 = "Luapula" if region == 32
replace region2 = "Gaborone" if region == 36
replace region2 = "Lobatse" if region == 37
replace region2 = "Francistown" if region == 38
replace region2 = "Jwaneng" if region == 39
replace region2 = "Selibe-Phikwe" if region == 40
replace region2 = "Southeast" if region == 41
replace region2 = "Kweneng" if region == 42
replace region2 = "Botswana Central" if region == 43
replace region2 = "Kgalagadi" if region == 44
replace region2 = "Chitipa" if region == 45
replace region2 = "Mzimba" if region == 46
replace region2 = "Nkhata Bay" if region == 47
replace region2 = "Kasunga" if region == 48
replace region2 = "Lilongwe" if region == 49
replace region2 = "Ntchisi" if region == 50
replace region2 = "Salima" if region == 51
replace region2 = "Ntcheu" if region == 52
replace region2 = "Zomba" if region == 53
replace region2 = "Chiradzulu" if region == 54
replace region2 = "Blantyre" if region == 55
replace region2 = "Chikwawa" if region == 56
replace region2 = "Nsanje" if region == 57
replace region2 = "Mulanje" if region == 58
replace region2 = "Mwanza" if region == 59
replace region2 = "Botha-Bothe" if region == 60
replace region2 = "Leribe" if region == 61
replace region2 = "Berea" if region == 62
replace region2 = "Maseru" if region == 63
replace region2 = "Mafeteng" if region == 64
replace region2 = "Mohales Hoek" if region == 65
replace region2 = "Quthing" if region == 66
replace region2 = "Qachas Nek" if region == 67
replace region2 = "Mokhotlong" if region == 68
replace region2 = "Thaba-Tseka" if region == 69
replace region2 = "Greater Accra" if region == 70
replace region2 = "Volta" if region == 71
replace region2 = "Northern" if region == 72
replace region2 = "Brong-Ahafo" if region == 73
replace region2 = "Ashanti" if region == 74
replace region2 = "Western" if region == 75
replace region2 = "Eastern" if region == 76
replace region2 = "Central" if region == 77
replace region2 = "Upper East" if region == 78
replace region2 = "Upper West" if region == 79
replace region2 = "Bamako" if region == 80
replace region2 = "Gao" if region == 81
replace region2 = "Kayes" if region == 82
replace region2 = "Kidal" if region == 83
replace region2 = "Kkro" if region == 84
replace region2 = "Mopti" if region == 85
replace region2 = "Segou" if region == 86
replace region2 = "Sikasso" if region == 87
replace region2 = "Tbtou" if region == 88
replace region2 = "Eastern Cape" if region == 100
replace region2 = "Free State" if region == 101
replace region2 = "Gauteng" if region == 102
replace region2 = "Kwa-Zulu Natal" if region == 103
replace region2 = "Mpumalanga" if region == 104
replace region2 = "Northern Cape" if region == 105
replace region2 = "Northern Province" if region == 106
replace region2 = "North West" if region == 107
replace region2 = "Western Cape" if region == 108
replace region2 = "Abia" if region == 110
replace region2 = "Akwa-Ibom" if region == 111
replace region2 = " Bauchi" if region == 112
replace region2 = "Bayelsa" if region == 113
replace region2 = "Benue" if region == 114
replace region2 = "Borno" if region == 115
replace region2 = "Delta" if region == 116
replace region2 = "Edo" if region == 117
replace region2 = "Enugu" if region == 118
replace region2 = "F.C.T." if region == 119
replace region2 = "Kano" if region == 120
replace region2 = "Katsina" if region == 121
replace region2 = "Kwara" if region == 122
replace region2 = "Lagos" if region == 123
replace region2 = "Niger" if region == 124
replace region2 = "Ogun" if region == 125
replace region2 = "Ondo" if region == 126
replace region2 = "Osun" if region == 127
replace region2 = "Oyo" if region == 128
replace region2 = "Plateau" if region == 129
replace region2 = "Rivers" if region == 130
replace region2 = "Sokoto" if region == 131
replace region2 = "Apac" if region == 150
replace region2 = "Arua" if region == 151
replace region2 = "Bugirl" if region == 152
replace region2 = "Bushenyi" if region == 153
replace region2 = "Busia" if region == 154
replace region2 = "Gulu" if region == 155
replace region2 = "Iganga" if region == 156
replace region2 = "Jinja" if region == 157
replace region2 = "Kabale" if region == 158
replace region2 = "Kabarole" if region == 159
replace region2 = "Kampala" if region == 160
replace region2 = "Kamuli" if region == 161
replace region2 = "Kasesse" if region == 162
replace region2 = "Katakwi" if region == 163
replace region2 = "Kibale" if region == 164
replace region2 = "Kiboga" if region == 165
replace region2 = "Kisoro" if region == 166
replace region2 = "Kitgum" if region == 167
replace region2 = "Kumi" if region == 168
replace region2 = "Lira" if region == 169
replace region2 = "Luwero" if region == 170
replace region2 = "Masaka" if region == 171
replace region2 = "Masindi" if region == 172
replace region2 = "Mbale" if region == 173
replace region2 = "Mbarara" if region == 174
replace region2 = "Mpigi" if region == 175
replace region2 = "Mubende" if region == 176
replace region2 = "Mukono" if region == 177
replace region2 = "Nakasongola" if region == 178
replace region2 = "Nebbi" if region == 179
replace region2 = "Ntungamo" if region == 180
replace region2 = "Pallisa" if region == 181
replace region2 = "Rakai" if region == 182
replace region2 = "Rukungirl" if region == 183
replace region2 = "Soroti" if region == 184
replace region2 = "Tororo" if region == 185
replace region2 = "Arusha" if region == 200
replace region2 = "Coast" if region == 201
replace region2 = "Dar es Salaam" if region == 202
replace region2 = "Dodoma" if region == 203
replace region2 = "Iringa" if region == 204
replace region2 = "Kagera" if region == 205
replace region2 = "Kaskazini Pemba" if region == 206
replace region2 = "Kaskazini Unguja" if region == 207
replace region2 = "Kigoma" if region == 208
replace region2 = "Kilimanjaro" if region == 209
replace region2 = "Kusini Pemba" if region == 210
replace region2 = "Kusini Unguja" if region == 211
replace region2 = "Lindi" if region == 212
replace region2 = "Mara" if region == 213
replace region2 = "Mbeya" if region == 214
replace region2 = "Mjini Magharibi" if region == 215
replace region2 = "Morogoro" if region == 216
replace region2 = "Mtwara" if region == 217
replace region2 = "Mwanza" if region == 218
replace region2 = "Rukwa" if region == 219
replace region2 = "Ruvuma" if region == 220
replace region2 = "Sinyanga" if region == 221
replace region2 = "Singida" if region == 222
replace region2 = "Tabora" if region == 223
replace region2 = "Tanga" if region == 224
replace region2 = "" if region == 999
}
save "afrobarometer\merged_r1_data_proc_georef.dta", replace

***************************************ROUND 2***************************************
import delim "afrobarometer\afb_r2_adm1_intersect.txt", clear delim(";")
quietly{
gen round2 = 1
gen year = substr(dateintr, -2, 2)
replace year = "2002" if year == "L!"
replace year = "2002" if year == "02"
replace year = "2003" if year == "03"
replace year = "2004" if year == "04"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep geoname__1 country region q83 q1a q1b  q2a q3b q4a q4b q9a q9b q9c q9d q9f q35a q35c q35d q37 q40 q42b q42c q43a q43c q43b q43d q43f q43g q43i q43h q43k q43j q56 q57 q87b q87a q54 year ID_adm1 ID_adm1_yr iso name_0 name_1 q11b q11c q27 q25c q38 q29c q41a
* Unify question names (create missings if this question was not asked in the respective round)
gen ea_svc_a = .
gen ea_svc_b = .
gen ea_svc_c = .
gen ea_svc_d = .
gen ea_fac_a = .
gen ea_fac_b = .
gen ea_fac_c = .
gen ea_fac_d = .
gen ea_fac_e = .
gen ea_fac_f = .
gen ea_fac_g = .
gen ea_sec_a = .
gen ea_sec_b = .
gen ea_sec_c = .
gen ea_sec_d = .
gen ea_sec_e = .
gen ea_road_a = .
gen ea_road_b = .
ren q83 q2
ren q4a q7
ren q4b q7b
ren q1a q4a
ren q1b q4b
ren q2a q5
ren q3b q6
ren q9a q8a
ren q9b q8b
ren q9c q8c
ren q9d q8d
ren q9f q8f
gen q27a = .
gen q27d = .
gen q27b = .
gen q27e = .
ren q35a q28a
ren q35c q28b
ren q35d q28c
ren q40 q41
ren q37 q40
ren q42b q42a
ren q42c q42b
ren q43a q52a
ren q43c q52c
ren q43b q52b
gen q52d = .
ren q43d q52e
ren q43f q52f
ren q43g q52g
ren q43i q52h
ren q43h q52i
ren q43k q52k
ren q43j q52j
gen q52l = .
gen q81a = .
gen q81b = .
gen q81c = .
ren q56 q88a
ren q57 q88b
gen q89a = .
gen q89b = .
ren q87b q90a
gen q89e = .
ren q87a q90b
ren q54 q102

gen q10a_x = . 
gen q11a_x = q11b
gen q11b_x = q11c
gen q13_x = q27
gen q15a_x = .
gen q15b_x = .
gen q15c_x = .
gen q16_x = .
gen q20b_x = q25c
gen q22_x = .
gen q30_x = q38
gen q24c_x = q29c
gen q49_x = .
gen q51a_x = q41a


gen round_afb = 2

* Unify names of geographic units over rounds
gen ISO = ""
replace ISO = "BWA" if country == 1
replace ISO = "GHA" if country == 2
replace ISO = "LSO" if country == 3
replace ISO = "MWI" if country == 4
replace ISO = "MLI" if country == 5
replace ISO = "NAM" if country == 6
replace ISO = "NGA" if country == 7
replace ISO = "ZAF" if country == 8
replace ISO = "TZA" if country == 9
replace ISO = "UGA" if country == 10
replace ISO = "ZMB" if country == 11
replace ISO = "ZWE" if country == 12
replace ISO = "CPV" if country == 13
replace ISO = "KEN" if country == 14
replace ISO = "MOZ" if country == 15
replace ISO = "SEN" if country == 16
gen country2 = ""
replace country2 = "Botswana" if country == 1
replace country2 = "Ghana" if country == 2
replace country2 = "Lesotho" if country == 3
replace country2 = "Malawi" if country == 4
replace country2 = "Mali" if country == 5
replace country2 = "Namibia" if country == 6
replace country2 = "Nigeria" if country == 7
replace country2 = "South Africa" if country == 8
replace country2 = "Tanzania" if country == 9
replace country2 = "Uganda" if country == 10
replace country2 = "Zambia" if country == 11
replace country2 = "Zimbabwe" if country == 12
replace country2 = "Cape Verde" if country == 13
replace country2 = "Kenya" if country == 14
replace country2 = "Mozambique" if country == 15
replace country2 = "Senegal" if country == 16
gen region2 = ""
replace region2 = "Santo Antao" if region == 50
replace region2 = "Sao Vicente" if region == 51
replace region2 = "Satiago" if region == 52
replace region2 = "Fogo" if region == 53
replace region2 = "West" if region == 60
replace region2 = "East" if region == 61
replace region2 = "Central" if region == 62
replace region2 = "North" if region == 63
replace region2 = "Niassa" if region == 80
replace region2 = "Cabo Delgado" if region == 81
replace region2 = "Nampula" if region == 82
replace region2 = "Zambezia" if region == 83
replace region2 = "Tete" if region == 84
replace region2 = "Manica" if region == 85
replace region2 = "Sofala" if region == 86
replace region2 = "Inhambane" if region == 87
replace region2 = "Gaza" if region == 88
replace region2 = "Maputo (Prov.)" if region == 89
replace region2 = "Maputo (Cid.)" if region == 90
replace region2 = "Western Cape" if region == 100
replace region2 = "Eastern Cape" if region == 101
replace region2 = "Northern Cape" if region == 102
replace region2 = "Free State" if region == 103
replace region2 = "KwaZulu Natal" if region == 104
replace region2 = "North West" if region == 105
replace region2 = "Gauteng" if region == 106
replace region2 = "Mpumulanga" if region == 107
replace region2 = "Limpopo" if region == 108
replace region2 = "Ashanti" if region == 120
replace region2 = "Brong Ahafo" if region == 121
replace region2 = "Central" if region == 122
replace region2 = "Eastern" if region == 123
replace region2 = "Greater Accra" if region == 124
replace region2 = "Northern" if region == 125
replace region2 = "Upper East" if region == 126
replace region2 = "Upper West" if region == 127
replace region2 = "Volta" if region == 128
replace region2 = "Western" if region == 129
replace region2 = "Central" if region == 140
replace region2 = "Northern" if region == 141
replace region2 = "Southern" if region == 142
replace region2 = "Bamako" if region == 160
replace region2 = "Gao" if region == 161
replace region2 = "Kayes" if region == 162
replace region2 = "Kidal" if region == 163
replace region2 = "Koulikoro" if region == 164
replace region2 = "Mopti" if region == 165
replace region2 = "Segou" if region == 166
replace region2 = "Sikasso" if region == 167
replace region2 = "Tombouctou" if region == 168
replace region2 = "Berea" if region == 180
replace region2 = "Butha-Buthe" if region == 181
replace region2 = "Leribe" if region == 182
replace region2 = "Mafeteng" if region == 183
replace region2 = "Maseru" if region == 184
replace region2 = "Mohale's Hoek" if region == 185
replace region2 = "Mokhotlong" if region == 186
replace region2 = "Qacha's nek" if region == 187
replace region2 = "Quthing" if region == 188
replace region2 = "Thaba-Tseka" if region == 189
replace region2 = "Arusha" if region == 200
replace region2 = "Dar" if region == 201
replace region2 = "Dodoma" if region == 202
replace region2 = "Iringa" if region == 203
replace region2 = "Kagera" if region == 204
replace region2 = "Kaskazini Pemba" if region == 205
replace region2 = "Kaskazini Unguja" if region == 206
replace region2 = "Kigoma" if region == 207
replace region2 = "Kilimajaro" if region == 208
replace region2 = "Kusini Pemba" if region == 209
replace region2 = "Kusini Unguja" if region == 210
replace region2 = "Lindi" if region == 211
replace region2 = "Manyara" if region == 212
replace region2 = "Mara" if region == 213
replace region2 = "Mbeya" if region == 214
replace region2 = "Mjini Magharibi" if region == 215
replace region2 = "Morogoro" if region == 216
replace region2 = "Mtwara" if region == 217
replace region2 = "Mwanza" if region == 218
replace region2 = "Pwani" if region == 219
replace region2 = "Dakar" if region == 220
replace region2 = "Diourbel" if region == 221
replace region2 = "Fatick" if region == 222
replace region2 = "Kaolack" if region == 223
replace region2 = "Kolda" if region == 224
replace region2 = "Louga" if region == 225
replace region2 = "Saint Louis" if region == 226
replace region2 = "Tambacounda" if region == 227
replace region2 = "Thies" if region == 228
replace region2 = "Ziguinchor" if region == 229
replace region2 = "Central" if region == 240
replace region2 = "Francistown" if region == 241
replace region2 = "Gaborone" if region == 242
replace region2 = "Ghanzi" if region == 243
replace region2 = "Jwaneng" if region == 244
replace region2 = "Kgalagadi" if region == 245
replace region2 = "Kgatleng" if region == 246
replace region2 = "Kweneng" if region == 247
replace region2 = "Lobatse" if region == 248
replace region2 = "North East" if region == 249
replace region2 = "North West" if region == 250
replace region2 = "Orapa" if region == 251
replace region2 = "Selibe Phikwe" if region == 252
replace region2 = "South East" if region == 253
replace region2 = "Southern" if region == 254
replace region2 = "Lagos" if region == 260
replace region2 = "NC" if region == 261
replace region2 = "NE" if region == 262
replace region2 = "NW" if region == 263
replace region2 = "SE" if region == 264
replace region2 = "SS" if region == 265
replace region2 = "SW" if region == 266
replace region2 = "BULAWAYO" if region == 280
replace region2 = "HARARE" if region == 281
replace region2 = "MANICALAND" if region == 282
replace region2 = "MASH CENTRAL" if region == 283
replace region2 = "MASH EAST" if region == 284
replace region2 = "MASH WEST" if region == 285
replace region2 = "MASVINGO" if region == 286
replace region2 = "MAT NORTH" if region == 287
replace region2 = "MAT SOUTH" if region == 288
replace region2 = "MIDLAND" if region == 289
replace region2 = "Central" if region == 300
replace region2 = "Copperbelt" if region == 301
replace region2 = "Eastern" if region == 302
replace region2 = "Luapula" if region == 303
replace region2 = "Lusaka" if region == 304
replace region2 = "North-Western" if region == 305
replace region2 = "Northern" if region == 306
replace region2 = "Southern" if region == 307
replace region2 = "Western" if region == 308
replace region2 = "Caprivi" if region == 320
replace region2 = "Erongo" if region == 321
replace region2 = "hardap" if region == 322
replace region2 = "Karas" if region == 323
replace region2 = "kavango" if region == 324
replace region2 = "Knoma" if region == 325
replace region2 = "Kunene" if region == 326
replace region2 = "Ohangwena" if region == 327
replace region2 = "Omaheke" if region == 328
replace region2 = "Omusati" if region == 329
replace region2 = "Oshana" if region == 330
replace region2 = "Oshikoto" if region == 331
replace region2 = "Otjozondjupa" if region == 332
replace region2 = "Central" if region == 380
replace region2 = "Coast" if region == 381
replace region2 = "Eastern" if region == 382
replace region2 = "Nairobi" if region == 383
replace region2 = "North Eastern" if region == 384
replace region2 = "Nyanza" if region == 385
replace region2 = "Rift-Valley" if region == 386
replace region2 = "Western" if region == 387
replace region2 = "Rukwa" if region == 450
replace region2 = "Ruvuma" if region == 451
replace region2 = "Shinyanga" if region == 452
replace region2 = "Singida" if region == 453
replace region2 = "Tabora" if region == 454
replace region2 = "Tanga" if region == 455
}
save "afrobarometer\merged_r2_data_proc_georef.dta", replace

***************************************ROUND 3***************************************
import delim "afrobarometer\afb_r3_adm1_intersect.txt", clear delim(";")
quietly{
gen round3 = 1
gen year = substr(dateintr, -2, 2)
replace year = "2005" if year == "05"
replace year = "2006" if year == "06"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep geoname__1 country region q3 q4a q4b q5 q6a q7a q7b q8a q8b q8c q8d q8f q36a q36b q36c q46 q47 q52b q52c q55a q55c q55b  q55d q55e q55f q55h q55g  q55i q81 q82 q85 q86 q79 year ID_adm1 ID_adm1_yr iso name_0 name_1 year ID_adm1 ID_adm1_yr q9b q9c q16 q25 q31b q45 q37 q32c q53a
* Unify question names (create missings if this question was not asked in the respective round)
gen ea_svc_a = .
gen ea_svc_b = .
gen ea_svc_c = .
gen ea_svc_d = .
gen ea_fac_a = .
gen ea_fac_b = .
gen ea_fac_c = .
gen ea_fac_d = .
gen ea_fac_e = .
gen ea_fac_f = .
gen ea_fac_g = .
gen ea_sec_a = .
gen ea_sec_b = .
gen ea_sec_c = .
gen ea_sec_d = .
gen ea_sec_e = .
gen ea_road_a = .
gen ea_road_b = .
ren q3 q2
ren q4a q4a
ren q4b q4b
ren q5 q5
ren q6a q6
ren q7a q7
ren q7b q7b
ren q8a q8a
ren q8b q8b
ren q8c q8c
ren q8d q8d
ren q8f q8f
gen q27a = .
gen q27d = .
gen q27b = .
gen q27e = .
ren q36a q28a
ren q36b q28b
ren q36c q28c
ren q46 q40
ren q47 q41
ren q52b q42a
ren q52c q42b
ren q55a q52a
ren q55c q52c
ren q55b q52b
gen q52d = .
ren q55d q52e
ren q55e q52f
ren q55f q52g
ren q55h q52h
ren q55g q52i
gen q52k = .
ren q55i q52j
gen q52l = .
gen q81a = .
gen q81b = .
gen q81c = .
ren q81 q88a
ren q82 q88b
gen q89a = .
gen q89b = .
ren q85 q90a
gen q89e = .
ren q86 q90b
ren q79 q102

gen q10a_x = .
gen q11a_x = q9b
gen q11b_x = q9c
gen q13_x = q16 
gen q15a_x = .
gen q15b_x = .
gen q15c_x = .
gen q16_x = q25 
gen q20b_x = q31b
gen q22_x = q45 
gen q30_x = q37
gen q24c_x = q32c
gen q49_x = .
gen q51a_x = q53a


gen round_afb = 3
* Unify names of geographic units over rounds
gen ISO = ""
replace ISO = "BWA" if country == 2
replace ISO = "GHA" if country == 4
replace ISO = "LSO" if country == 6
replace ISO = "MWI" if country == 8
replace ISO = "MLI" if country == 9
replace ISO = "NAM" if country == 11
replace ISO = "NGA" if country == 12
replace ISO = "ZAF" if country == 14
replace ISO = "TZA" if country == 15
replace ISO = "UGA" if country == 16
replace ISO = "ZMB" if country == 17
replace ISO = "ZWE" if country == 18
replace ISO = "CPV" if country == 3
replace ISO = "KEN" if country == 5
replace ISO = "MOZ" if country == 10
replace ISO = "SEN" if country == 13
replace ISO = "BEN" if country == 1
replace ISO = "MDG" if country == 7
gen country2 = ""
replace country2 = "Benin" if country == 1
replace country2 = "Botswana" if country == 2
replace country2 = "Cape Verde" if country == 3
replace country2 = "Ghana" if country == 4
replace country2 = "Kenya" if country == 5
replace country2 = "Lesotho" if country == 6
replace country2 = "Madagascar" if country == 7
replace country2 = "Malawi" if country == 8
replace country2 = "Mali" if country == 9
replace country2 = "Mozambique" if country == 10
replace country2 = "Namibia" if country == 11
replace country2 = "Nigeria" if country == 12
replace country2 = "Senegal" if country == 13
replace country2 = "South Africa" if country == 14
replace country2 = "Tanzania" if country == 15
replace country2 = "Uganda" if country == 16
replace country2 = "Zambia" if country == 17
replace country2 = "Zimbabwe" if country == 18
gen region2 = ""
replace region2 = "Eastern Cape" if region == 100
replace region2 = "Free State" if region == 101
replace region2 = "Gauteng" if region == 102
replace region2 = "KwaZulu Natal" if region == 103
replace region2 = "Limpopo" if region == 104
replace region2 = "Mpumalanga" if region == 105
replace region2 = "North West" if region == 106
replace region2 = "Northern Cape" if region == 107
replace region2 = "Western Cape" if region == 108
replace region2 = "Alibori" if region == 120
replace region2 = "Atacora" if region == 121
replace region2 = "Atlnatique" if region == 122
replace region2 = "Borgou" if region == 123
replace region2 = "Collines" if region == 124
replace region2 = "Couffo" if region == 125
replace region2 = "Donga" if region == 126
replace region2 = "Littoral" if region == 127
replace region2 = "Mono" if region == 128
replace region2 = "Oueme" if region == 129
replace region2 = "Plateau" if region == 130
replace region2 = "Zou" if region == 131
replace region2 = "Central" if region == 140
replace region2 = "Chobe" if region == 141
replace region2 = "Francis Town" if region == 142
replace region2 = "Gaborone" if region == 143
replace region2 = "Ghanzi" if region == 144
replace region2 = "Jwaneng" if region == 145
replace region2 = "Kgalagadi" if region == 146
replace region2 = "Kgatleng" if region == 147
replace region2 = "Kweneng" if region == 148
replace region2 = "Lobatse" if region == 149
replace region2 = "North east" if region == 150
replace region2 = "Ngamiland" if region == 151
replace region2 = "Selibe Phikwe" if region == 153
replace region2 = "South East" if region == 154
replace region2 = "Southern" if region == 155
replace region2 = "Santo Antão" if region == 160
replace region2 = "São Vicente" if region == 161
replace region2 = "Santiago-Interior" if region == 162
replace region2 = "Santiago-Praia" if region == 163
replace region2 = "Fogo" if region == 164
replace region2 = "Western" if region == 180
replace region2 = "Central" if region == 181
replace region2 = "Greater Accra" if region == 182
replace region2 = "Volta" if region == 183
replace region2 = "Eastern" if region == 184
replace region2 = "Ashanti" if region == 185
replace region2 = "Brong Ahafo" if region == 186
replace region2 = "Northern" if region == 187
replace region2 = "Upper East" if region == 188
replace region2 = "Upper West" if region == 189
replace region2 = "Nairobi" if region == 200
replace region2 = "Central" if region == 201
replace region2 = "Eastern" if region == 202
replace region2 = "Rift Valley" if region == 203
replace region2 = "Nyanza" if region == 204
replace region2 = "Western" if region == 205
replace region2 = "North Eastern" if region == 206
replace region2 = "Coast" if region == 207
replace region2 = "Maseru" if region == 220
replace region2 = "Mafeteng" if region == 221
replace region2 = "Mohale'Hoek" if region == 222
replace region2 = "Quthing" if region == 223
replace region2 = "Qacha's Nek" if region == 224
replace region2 = "Mokhotlong" if region == 225
replace region2 = "Butha Buthe" if region == 226
replace region2 = "Leribe" if region == 227
replace region2 = "Berea" if region == 228
replace region2 = "Thaba-Tseka" if region == 229
replace region2 = "Antananarivo" if region == 240
replace region2 = "Fianarantsoa" if region == 241
replace region2 = "Toamasina" if region == 242
replace region2 = "Mahajanga" if region == 243
replace region2 = "Toliary" if region == 244
replace region2 = "Antsiranana" if region == 245
replace region2 = "South" if region == 260
replace region2 = "Central" if region == 261
replace region2 = "North" if region == 262
replace region2 = "Bamako" if region == 280
replace region2 = "Kayes" if region == 281
replace region2 = "Koulikoro" if region == 282
replace region2 = "Sikasso" if region == 283
replace region2 = "Ségou" if region == 284
replace region2 = "Mopti" if region == 285
replace region2 = "Tombouctou" if region == 286
replace region2 = "Gao" if region == 287
replace region2 = "Kidal" if region == 288
replace region2 = "Maputo province" if region == 300
replace region2 = "Maputo City" if region == 301
replace region2 = "Gaza" if region == 302
replace region2 = "Inhambane" if region == 303
replace region2 = "Sofala" if region == 304
replace region2 = "Tete" if region == 305
replace region2 = "Manica" if region == 306
replace region2 = "Zambezia" if region == 307
replace region2 = "Nampula" if region == 308
replace region2 = "Niassa" if region == 309
replace region2 = "Cabo Delgado" if region == 310
replace region2 = "Caprivi" if region == 320
replace region2 = "Erongo" if region == 321
replace region2 = "Hardap" if region == 322
replace region2 = "Karas" if region == 323
replace region2 = "Kavango" if region == 324
replace region2 = "Khomas" if region == 325
replace region2 = "Kunene" if region == 326
replace region2 = "Ohangwena" if region == 327
replace region2 = "Omaheke" if region == 328
replace region2 = "Omusati" if region == 329
replace region2 = "Oshana" if region == 330
replace region2 = "Oshikoto" if region == 331
replace region2 = "Otjozundjupa" if region == 332
replace region2 = "Lagos" if region == 340
replace region2 = "Ogun" if region == 341
replace region2 = "Oyo" if region == 342
replace region2 = "Osun" if region == 343
replace region2 = "Ondo" if region == 344
replace region2 = "Ekiti" if region == 345
replace region2 = "Enugu" if region == 346
replace region2 = "Anambra" if region == 347
replace region2 = "Imo" if region == 348
replace region2 = "Abia" if region == 349
replace region2 = "Akwa-Ibom" if region == 350
replace region2 = "Bayelsa" if region == 351
replace region2 = "Cross-River" if region == 352
replace region2 = "Delta" if region == 353
replace region2 = "Edo" if region == 354
replace region2 = "Rivers" if region == 355
replace region2 = "Kano" if region == 356
replace region2 = "Sokoto" if region == 357
replace region2 = "Kaduna" if region == 358
replace region2 = "Katsina" if region == 359
replace region2 = "Dakar" if region == 360
replace region2 = "Diourbel" if region == 361
replace region2 = "Fatick" if region == 362
replace region2 = "Kaolack" if region == 363
replace region2 = "Kolda" if region == 364
replace region2 = "Louga" if region == 365
replace region2 = "Matam" if region == 366
replace region2 = "Saint Louis" if region == 367
replace region2 = "Tambacounda" if region == 368
replace region2 = "Thies" if region == 369
replace region2 = "Ziguinchor" if region == 370
replace region2 = "Dodoma" if region == 380
replace region2 = "Arusha" if region == 381
replace region2 = "Kilimanjaro" if region == 382
replace region2 = "Tanga" if region == 383
replace region2 = "Morogoro" if region == 384
replace region2 = "Pwani" if region == 385
replace region2 = "Dar es Salaam" if region == 386
replace region2 = "Lindi" if region == 387
replace region2 = "Mtwara" if region == 388
replace region2 = "Ruvuma" if region == 389
replace region2 = "Iringa" if region == 390
replace region2 = "Mbeya" if region == 391
replace region2 = "Singida" if region == 392
replace region2 = "Tabora" if region == 393
replace region2 = "Rukwa" if region == 394
replace region2 = "Kigoma" if region == 395
replace region2 = "Shinyanga" if region == 396
replace region2 = "Kagera" if region == 397
replace region2 = "Mwanza" if region == 398
replace region2 = "Mara" if region == 399
replace region2 = "Central" if region == 400
replace region2 = "East" if region == 401
replace region2 = "North" if region == 402
replace region2 = "West" if region == 404
replace region2 = "Lusaka" if region == 420
replace region2 = "Central" if region == 421
replace region2 = "Copper Belt" if region == 422
replace region2 = "Eastern" if region == 423
replace region2 = "Luapula" if region == 424
replace region2 = "Northern" if region == 425
replace region2 = "North Western" if region == 426
replace region2 = "Southern" if region == 427
replace region2 = "Western" if region == 428
replace region2 = "Harare" if region == 440
replace region2 = "Bulawayo" if region == 441
replace region2 = "Midlands" if region == 442
replace region2 = "Masvingo" if region == 443
replace region2 = "Mashonaland East" if region == 444
replace region2 = "Mashonaland West" if region == 445
replace region2 = "Mashonaland Central" if region == 446
replace region2 = "Matebeleland South" if region == 447
replace region2 = "Matebeland North" if region == 448
replace region2 = "Manicaland" if region == 449
replace region2 = "Manyara" if region == 540
replace region2 = "North Unguja" if region == 541
replace region2 = "South Unguja" if region == 542
replace region2 = "Urban West" if region == 543
replace region2 = "North Pemba" if region == 544
replace region2 = "South Pemba" if region == 545
replace region2 = "Zamfara" if region == 550
replace region2 = "Bauchi" if region == 551
replace region2 = "Borno" if region == 552
replace region2 = "Adamawa" if region == 553
replace region2 = "Taraba" if region == 554
replace region2 = "Plateau" if region == 555
replace region2 = "Benue" if region == 556
replace region2 = "Kogi" if region == 557
replace region2 = "Kwara" if region == 558
replace region2 = "Niger" if region == 559
replace region2 = "FCT" if region == 560
}
save "afrobarometer\merged_r3_data_proc_georef.dta", replace

***************************************ROUND 4***************************************
import delimited "afrobarometer\afb_r4_extra_adm1_intersect.txt", clear delim(";")
save "afrobarometer\afb_r4_extra_adm1_intersect.dta", replace
import delim "afrobarometer\afb_r4_adm1_intersect.txt", clear delim(";")
quietly{
drop if country == 6 |  country == 7 |  country == 8 | country == 13 | country == 16 
append using "afrobarometer\afb_r4_extra_adm1_intersect.dta", force gen (extraset)
gen round4 = 1
gen year = substr(dateintr, -2, 2)
replace year = "2009" if year == "09"
replace year = "2008" if year == "08"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep geoname__1 ID_adm1_yr ID_adm1 id_0 id_1 iso name_0 name_1 hasc_1 engtype_1 type_1 year dateintr country region ea_svc_a ea_svc_b ea_svc_c ea_svc_d ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_sec_a ea_sec_b ea_road q3 q4a q4b q5 q6a q7a q7b q8a q8b q8c q8d q8e q29a q29b q29c q42a q43 q44a q44b q49a q49c q49b q49d q49e q49f q49g q49i q49h q82 q83 q85  q86 q79 q9b q9c q13 q15a q15b q15c q19 q23b q71 q30 q25c q47 q46
* Unify question names (create missings if this question was not asked in the respective round)
gen ea_fac_f = .
gen ea_fac_g = .
gen ea_sec_c = .
gen ea_sec_d = .
gen ea_sec_e = .
gen ea_road_b = .
ren ea_road ea_road_a
ren q6a q6
ren q3 q2
ren q7a q7
ren q7b q7b
ren q8e q8f
gen q27a = .
gen q27d = .
gen q27b = .
gen q27e = .
ren q29a q28a
ren q29b q28b
ren q29c q28c
ren q42a q40
ren q43 q41
ren q44a q42a
ren q44b q42b
ren q49a q52a
ren q49c q52c
ren q49b q52b
gen q52d = .
ren q49d q52e
ren q49e q52f
ren q49f q52g
ren q49g q52h
ren q49i q52k
ren q49h q52j
gen q52i = .
gen q81a = .
gen q81b = .
gen q81c = .
ren q82 q88a
ren q83 q88b
gen q89a = .
gen q89b = .
ren q85 q90a
gen q89e = .
ren q86 q90b
ren q79 q102

gen q10a_x = .
gen q11a_x = q9b 
gen q11b_x = q9c 
gen q13_x = q13 
gen q15a_x = q15a 
gen q15b_x = q15b 
gen q15c_x = q15c 
gen q16_x = q19
gen q20b_x = q23b 
gen q22_x = q71
gen q30_x = q30 
gen q24c_x = q25c 
gen q49_x = q47
gen q51a_x = q46


gen round_afb = 4
* Unify names of geographic units over rounds
gen ISO = ""
replace ISO = "BWA" if country == 2
replace ISO = "GHA" if country == 5
replace ISO = "LSO" if country == 7
replace ISO = "MWI" if country == 10
replace ISO = "MLI" if country == 11
replace ISO = "NAM" if country == 13
replace ISO = "NGA" if country == 14
replace ISO = "ZAF" if country == 16
replace ISO = "TZA" if country == 17
replace ISO = "UGA" if country == 18
replace ISO = "ZMB" if country == 19
replace ISO = "ZWE" if country == 20
replace ISO = "CPV" if country == 4
replace ISO = "KEN" if country == 6
replace ISO = "MOZ" if country == 12
replace ISO = "SEN" if country == 15
replace ISO = "BEN" if country == 1
replace ISO = "MDG" if country == 9
replace ISO = "BFA" if country == 3
replace ISO = "LBR" if country == 8
gen country2 = ""
replace country2 = "Benin" if country == 1
replace country2 = "Botswana" if country == 2
replace country2 = "Burkina Faso" if country == 3
replace country2 = "Cape Verde" if country == 4
replace country2 = "Ghana" if country == 5
replace country2 = "Kenya" if country == 6
replace country2 = "Lesotho" if country == 7
replace country2 = "Liberia" if country == 8
replace country2 = "Madagascar" if country == 9
replace country2 = "Malawi" if country == 10
replace country2 = "Mali" if country == 11
replace country2 = "Mozambique" if country == 12
replace country2 = "Namibia" if country == 13
replace country2 = "Nigeria" if country == 14
replace country2 = "Senegal" if country == 15
replace country2 = "South Africa" if country == 16
replace country2 = "Tanzania" if country == 17
replace country2 = "Uganda" if country == 18
replace country2 = "Zambia" if country == 19
replace country2 = "Zimbabwe" if country == 20
gen region2 = ""
replace region2 = "Alibori" if region == 100
replace region2 = "Atacora" if region == 101
replace region2 = "Atlantique" if region == 102
replace region2 = "Borgou" if region == 103
replace region2 = "Collines" if region == 104
replace region2 = "Couffo" if region == 105
replace region2 = "Donga" if region == 106
replace region2 = "Littoral" if region == 107
replace region2 = "Mono" if region == 108
replace region2 = "Oueme" if region == 109
replace region2 = "Plateau" if region == 110
replace region2 = "Zou" if region == 111
replace region2 = "Barolong" if region == 140
replace region2 = "Central Bobonong" if region == 141
replace region2 = "Central Boteti" if region == 142
replace region2 = "Central Mahalapye" if region == 143
replace region2 = "Central Serowe/Palapye" if region == 144
replace region2 = "Central Tutume" if region == 145
replace region2 = "Chobe" if region == 146
replace region2 = "Francistown" if region == 147
replace region2 = "Gaborone" if region == 148
replace region2 = "Ghanzi" if region == 149
replace region2 = "Jwaneng" if region == 150
replace region2 = "Kgalagadi South" if region == 151
replace region2 = "Kgalagadi North" if region == 152
replace region2 = "Kgatleng" if region == 153
replace region2 = "Kweneng East" if region == 154
replace region2 = "Kweneng West" if region == 155
replace region2 = "Lobatse" if region == 156
replace region2 = "Ngamiland East" if region == 157
replace region2 = "Ngamiland West" if region == 158
replace region2 = "Ngwaketse" if region == 159
replace region2 = "Ngwaketse West" if region == 160
replace region2 = "North East" if region == 161
replace region2 = "Selibe Pikwe" if region == 162
replace region2 = "South East" if region == 163
replace region2 = "Boucle du Mouhoun" if region == 180
replace region2 = "Cascades" if region == 181
replace region2 = "Centre" if region == 182
replace region2 = "Centre-Est" if region == 183
replace region2 = "Centre-Nord" if region == 184
replace region2 = "Centre-Ouest" if region == 185
replace region2 = "Centre-Sud" if region == 186
replace region2 = "Est" if region == 187
replace region2 = "Hauts-Bassins" if region == 188
replace region2 = "Nord" if region == 189
replace region2 = "Plateau Central" if region == 190
replace region2 = "Sahel" if region == 191
replace region2 = "Sud-Ouest" if region == 192
replace region2 = "Santo Antao" if region == 220
replace region2 = "Sao Vicente" if region == 221
replace region2 = "Santiago - Interior" if region == 222
replace region2 = "Santiago - Praia" if region == 223
replace region2 = "Fogo" if region == 224
replace region2 = "Western" if region == 260
replace region2 = "Central" if region == 261
replace region2 = "Greater Accra" if region == 262
replace region2 = "Volta" if region == 263
replace region2 = "Eastern" if region == 264
replace region2 = "Ashanti" if region == 265
replace region2 = "Brong Ahafo" if region == 266
replace region2 = "Northern" if region == 267
replace region2 = "Upper East" if region == 268
replace region2 = "Upper West" if region == 269
replace region2 = "Nairobi" if region == 300
replace region2 = "Central" if region == 301
replace region2 = "Eastern" if region == 302
replace region2 = "Rift Valley" if region == 303
replace region2 = "Nyanza" if region == 304
replace region2 = "Western" if region == 305
replace region2 = "North Eastern" if region == 306
replace region2 = "Coast" if region == 307
replace region2 = "Butha-Buthe" if region == 340
replace region2 = "Leribe" if region == 341
replace region2 = "Berea" if region == 342
replace region2 = "Maseru" if region == 343
replace region2 = "Mafeteng" if region == 344
replace region2 = "Mohale's Hoek" if region == 345
replace region2 = "Quthing" if region == 346
replace region2 = "Qacha's Nek" if region == 347
replace region2 = "Mokhotlong" if region == 348
replace region2 = "Thaba-Tseka" if region == 349
replace region2 = "Bomi" if region == 380
replace region2 = "Bong" if region == 381
replace region2 = "Gbarpolu" if region == 382
replace region2 = "Grand Bassa" if region == 383
replace region2 = "Grand Cape Mount" if region == 384
replace region2 = "Grand Gedeh" if region == 385
replace region2 = "Grand Kru" if region == 386
replace region2 = "Lofa" if region == 387
replace region2 = "Margibi" if region == 388
replace region2 = "Maryland" if region == 389
replace region2 = "Montserrado" if region == 390
replace region2 = "Nimba" if region == 391
replace region2 = "Rivercess" if region == 392
replace region2 = "River Gee" if region == 393
replace region2 = "Sinoe" if region == 394
replace region2 = "Antananarivo" if region == 420
replace region2 = "Fianarantsoa" if region == 421
replace region2 = "Toamasina" if region == 422
replace region2 = "Mahajanga" if region == 423
replace region2 = "Toliary" if region == 424
replace region2 = "Antsiranana" if region == 425
replace region2 = "Central" if region == 460
replace region2 = "North" if region == 461
replace region2 = "South" if region == 462
replace region2 = "Kayes" if region == 501
replace region2 = "Koulikoro" if region == 502
replace region2 = "Sikasso" if region == 503
replace region2 = "Segou" if region == 504
replace region2 = "Mopti" if region == 505
replace region2 = "Tombouctou" if region == 506
replace region2 = "Gao" if region == 507
replace region2 = "Kidal" if region == 508
replace region2 = "Bamako" if region == 509
replace region2 = "Maputo" if region == 540
replace region2 = "Maputo City" if region == 541
replace region2 = "Gaza" if region == 542
replace region2 = "Inhambane" if region == 543
replace region2 = "Sofala" if region == 544
replace region2 = "Tete" if region == 545
replace region2 = "Manica" if region == 546
replace region2 = "Zambezia" if region == 547
replace region2 = "Nampula" if region == 548
replace region2 = "Niassa" if region == 549
replace region2 = "Cabo Delgado" if region == 550
replace region2 = "Caprivi" if region == 580
replace region2 = "Erongo" if region == 581
replace region2 = "Hardap" if region == 582
replace region2 = "Karas" if region == 583
replace region2 = "Kavango" if region == 584
replace region2 = "Khomas" if region == 585
replace region2 = "Kunene" if region == 586
replace region2 = "Ohangwena" if region == 587
replace region2 = "Omaheke" if region == 588
replace region2 = "Omusati" if region == 589
replace region2 = "Oshana" if region == 590
replace region2 = "Oshikoto" if region == 591
replace region2 = "Otjozondjupa" if region == 592
replace region2 = "Abia" if region == 620
replace region2 = "Adamawa" if region == 621
replace region2 = "Akwa-Ibom" if region == 622
replace region2 = "Anambra" if region == 623
replace region2 = "Bauchi" if region == 624
replace region2 = "Bayelsa" if region == 625
replace region2 = "Benue" if region == 626
replace region2 = "Borno" if region == 627
replace region2 = "Cross-River" if region == 628
replace region2 = "Delta" if region == 629
replace region2 = "Ebonyi" if region == 630
replace region2 = "Edo" if region == 631
replace region2 = "Ekiti" if region == 632
replace region2 = "Enugu" if region == 633
replace region2 = "FCT" if region == 634
replace region2 = "Gombe" if region == 635
replace region2 = "Imo" if region == 636
replace region2 = "Jigawa" if region == 637
replace region2 = "Kaduna" if region == 638
replace region2 = "Kano" if region == 639
replace region2 = "Katsina" if region == 640
replace region2 = "Kebbi" if region == 641
replace region2 = "Kogi" if region == 642
replace region2 = "Kwara" if region == 643
replace region2 = "Lagos" if region == 644
replace region2 = "Nassarawa" if region == 645
replace region2 = "Niger" if region == 646
replace region2 = "Ogun" if region == 647
replace region2 = "Ondo" if region == 648
replace region2 = "Osun" if region == 649
replace region2 = "Oyo" if region == 650
replace region2 = "Plateau" if region == 651
replace region2 = "Rivers" if region == 652
replace region2 = "Sokoto" if region == 653
replace region2 = "Taraba" if region == 654
replace region2 = "Yobe" if region == 655
replace region2 = "Zamfara" if region == 656
replace region2 = "Dakar" if region == 660
replace region2 = "Diourbel" if region == 661
replace region2 = "Fatick" if region == 662
replace region2 = "Kaolack" if region == 663
replace region2 = "Kolda" if region == 664
replace region2 = "Louga" if region == 665
replace region2 = "Matam" if region == 666
replace region2 = "Saint-Louis" if region == 667
replace region2 = "Tambacounda" if region == 668
replace region2 = "Thies" if region == 669
replace region2 = "Ziguinchor" if region == 670
replace region2 = "Eastern Cape" if region == 700
replace region2 = "Free State" if region == 701
replace region2 = "Gauteng" if region == 702
replace region2 = "Kwazulu Natal" if region == 703
replace region2 = "Limpopo" if region == 704
replace region2 = "Mpumalanga" if region == 705
replace region2 = "North West" if region == 706
replace region2 = "Northern Cape" if region == 707
replace region2 = "Western Cape" if region == 708
replace region2 = "Dodoma" if region == 740
replace region2 = "Arusha" if region == 741
replace region2 = "Kilimanjaro" if region == 742
replace region2 = "Tanga" if region == 743
replace region2 = "Morogoro" if region == 744
replace region2 = "Coast(Pwani)" if region == 745
replace region2 = "Dares Salaam" if region == 746
replace region2 = "Lindi" if region == 747
replace region2 = "Mtwara" if region == 748
replace region2 = "Ruvuma" if region == 749
replace region2 = "Iringa" if region == 750
replace region2 = "Mbeya" if region == 751
replace region2 = "Singida" if region == 752
replace region2 = "Tabora" if region == 753
replace region2 = "Rukwa" if region == 754
replace region2 = "Kigoma" if region == 755
replace region2 = "Shinyanga" if region == 756
replace region2 = "Kagera" if region == 757
replace region2 = "Mwanza" if region == 758
replace region2 = "Mara" if region == 759
replace region2 = "Manyara" if region == 760
replace region2 = "North Unguja" if region == 761
replace region2 = "South Unguja" if region == 762
replace region2 = "Urban West" if region == 763
replace region2 = "North Pemba" if region == 764
replace region2 = "South Pemba" if region == 765
replace region2 = "Central" if region == 780
replace region2 = "West" if region == 781
replace region2 = "North" if region == 782
replace region2 = "East" if region == 783
replace region2 = "Kampala" if region == 784
replace region2 = "Lusaka" if region == 820
replace region2 = "Central" if region == 821
replace region2 = "Copperbelt" if region == 822
replace region2 = "Eastern" if region == 823
replace region2 = "Luapula" if region == 824
replace region2 = "Northern" if region == 825
replace region2 = "North-Western" if region == 826
replace region2 = "Southern" if region == 827
replace region2 = "Western" if region == 828
replace region2 = "Harare" if region == 860
replace region2 = "Bulawayo" if region == 861
replace region2 = "Midlands" if region == 862
replace region2 = "Masvingo" if region == 863
replace region2 = "Mashonaland East" if region == 864
replace region2 = "Mashonaland West" if region == 865
replace region2 = "Mashonaland Central" if region == 866
replace region2 = "Matebeland South" if region == 867
replace region2 = "Matebeland North" if region == 868
replace region2 = "Manicaland" if region == 869
}

save "afrobarometer\merged_r4_data_proc_georef.dta", replace

***************************************ROUND 5***************************************
import delimited "afrobarometer\afb_r5_extra_adm1_intersect.txt", clear delim(";")
quietly{
save "afrobarometer\afb_r5_extra_adm1_intersect.dta", replace
import delimited "afrobarometer\afb_r5_extra2_adm1_intersect.txt", clear delim(";")
save "afrobarometer\afb_r5_extra2_adm1_intersect.dta", replace
import delimited "afrobarometer\afb_r5_adm1_intersect.txt", clear delim(";")
drop if country == 4 | country == 5 | country == 6 |  country == 7 |  country == 8 | country == 11 |  country == 13 |  country == 16 | country == 21 |  country == 22 |  country == 25 |  country == 29 
append using "afrobarometer\afb_r5_extra_adm1_intersect.dta", force gen (extraset)
append using "afrobarometer\afb_r5_extra2_adm1_intersect.dta", force gen (extraset2)
gen round5 = 1
gen year = substr(dateintr, -2, 2)
replace year = "2013" if year == "13"
replace year = "2012" if year == "12"
replace year = "2011" if year == "11"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep geoname__1 ID_adm1_yr ID_adm1 year id_0 id_1 country_al name_0 name_1 iso region ea_svc_a ea_svc_c ea_svc_d ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_sec_a ea_sec_b ea_sec_c ea_sec_d ea_sec_e ea_road  q2 q3a q3b q4 q5a q6a q6b q8a q8b q8c q8d q8e q31a q31b q31c q42 q43 q48a q48b q59a q59c q59b q59d q59e q59f q59g q59h q59i  q59j q85a q85b q89a q89b q84 q9a q10a q10b q14 q17a q17b q17c q19 q26b q28 q32 q30c q54 q56a
ren country_al country
* Unify question names (create missings if this question was not asked in the respective round)
gen ea_fac_f = .
gen ea_fac_g = .
ren ea_road ea_road_a
gen ea_road_b = .
ren q3a q4a
ren q3b q4b
ren q4 q5
ren q5a q6
ren q6a q7
ren q6b q7b
ren q8e q8f
gen q27a = .
gen q27d = .
gen q27b = .
gen q27e = .
ren q31a q28a
ren q31b q28b
ren q31c q28c
ren q42 q40
ren q43 q41
ren q48a q42a
ren q48b q42b
ren q59a q52a
ren q59c q52c
ren q59b q52b
ren q59d q52d
ren q59e q52e
ren q59f q52f
ren q59g q52g
ren q59h q52h
ren q59i q52i
gen q52k = .
ren q59j q52j
gen q52l = .
gen q81a = .
gen q81b = .
gen q81c = .
ren q85a q88a
ren q85b q88b
ren q89a q90a
gen q89e = .
ren q89b q90b
gen q89a = .
gen q89b = .
ren q84 q102

gen q10a_x = q9a 
gen q11a_x = q10a 
gen q11b_x = q10b 
gen q13_x = q14 
gen q15a_x = q17a 
gen q15b_x = q17b 
gen q15c_x = q17c 
gen q16_x = q19 
gen q20b_x = q26b 
gen q22_x = q28 
gen q30_x = q32 
gen q24c_x = q30c 
gen q49_x = q54 
gen q51a_x = q56a



gen round_afb = 5
* Unify names of geographic units over rounds
gen ISO = ""
gen country2 = ""
replace country2 = "Algeria" if country == 1
replace country2 = "Benin" if country == 2
replace country2 = "Botswana " if country == 3
replace country2 = "Burkina Faso" if country == 4
replace country2 = "Burundi" if country == 5
replace country2 = "Cameroon" if country == 6
replace country2 = "Cape Verde" if country == 7
replace country2 = "Côte d'Ivoire" if country == 8
replace country2 = "Egypt" if country == 9
replace country2 = "Ghana" if country == 11
replace country2 = "Guinea" if country == 12
replace country2 = "Kenya" if country == 13
replace country2 = "Lesotho" if country == 14
replace country2 = "Liberia" if country == 15
replace country2 = "Madagascar" if country == 16
replace country2 = "Malawi" if country == 17
replace country2 = "Mali" if country == 18
replace country2 = "Mauritius" if country == 19
replace country2 = "Morocco" if country == 20
replace country2 = "Mozambique" if country == 21
replace country2 = "Namibia" if country == 22
replace country2 = "Niger" if country == 23
replace country2 = "Nigeria" if country == 24
replace country2 = "Senegal" if country == 25
replace country2 = "Sierra Leone" if country == 26
replace country2 = "South Africa" if country == 27
replace country2 = "Sudan" if country == 28
replace country2 = "Swaziland" if country == 29
replace country2 = "Tanzania" if country == 30
replace country2 = "Togo" if country == 31
replace country2 = "Tunisia" if country == 32
replace country2 = "Uganda" if country == 33
replace country2 = "Zambia" if country == 34
replace country2 = "Zimbabwe" if country == 35
replace ISO = "DZA" if country == 1
replace ISO = "BEN" if country == 2
replace ISO = "BWA" if country == 3
replace ISO = "BFA" if country == 4
replace ISO = "BDI" if country == 5
replace ISO = "CMR" if country == 6
replace ISO = "CPV" if country == 7
replace ISO = "CIV" if country == 8
replace ISO = "EGY" if country == 9
replace ISO = "GHA" if country == 11
replace ISO = "GIN" if country == 12
replace ISO = "KEN" if country == 13
replace ISO = "LSO" if country == 14
replace ISO = "LBR" if country == 15
replace ISO = "MDG" if country == 16
replace ISO = "MWI" if country == 17
replace ISO = "MLI" if country == 18
replace ISO = "MUS" if country == 19
replace ISO = "MAR" if country == 20
replace ISO = "MOZ" if country == 21
replace ISO = "NAM" if country == 22
replace ISO = "NER" if country == 23
replace ISO = "NGA" if country == 24
replace ISO = "SEN" if country == 25
replace ISO = "SLE" if country == 26
replace ISO = "ZAF" if country == 27
replace ISO = "SDN" if country == 28
replace ISO = "SWZ" if country == 29
replace ISO = "TZA" if country == 30
replace ISO = "TGO" if country == 31
replace ISO = "TUN" if country == 32
replace ISO = "UGA" if country == 33
replace ISO = "ZMB" if country == 34
replace ISO = "ZWE" if country == 35
gen region2 = ""
replace region2 = "Alibori" if region == 100
replace region2 = "Atacora" if region == 101
replace region2 = "Atlantique" if region == 102
replace region2 = "Borgou" if region == 103
replace region2 = "Collines" if region == 104
replace region2 = "Couffo" if region == 105
replace region2 = "Donga" if region == 106
replace region2 = "Littoral" if region == 107
replace region2 = "Mono" if region == 108
replace region2 = "Oueme" if region == 109
replace region2 = "Plateau" if region == 110
replace region2 = "Zou" if region == 111
replace region2 = "Barolong" if region == 140
replace region2 = "Central  Bobonong" if region == 141
replace region2 = "Central Boteti" if region == 142
replace region2 = "Central Mahalapye" if region == 143
replace region2 = "Central Serowe/Palapye" if region == 144
replace region2 = "Central Tutume" if region == 145
replace region2 = "Chobe" if region == 146
replace region2 = "Francistown" if region == 147
replace region2 = "Gaborone" if region == 148
replace region2 = "Ghanzi" if region == 149
replace region2 = "Jwaneng" if region == 150
replace region2 = "Kgalagadi North" if region == 151
replace region2 = "Kgalagadi South" if region == 152
replace region2 = "Kgatleng" if region == 153
replace region2 = "Kweneng East" if region == 154
replace region2 = "Kweneng West" if region == 155
replace region2 = "Lobatse" if region == 156
replace region2 = "Ngamiland East" if region == 157
replace region2 = "Ngamiland West" if region == 158
replace region2 = "Ngwaketse" if region == 159
replace region2 = "Ngwaketse West" if region == 160
replace region2 = "North East" if region == 161
replace region2 = "Seleibe Phikwe" if region == 162
replace region2 = "South East" if region == 163
replace region2 = "Boucle du Mouhoun" if region == 180
replace region2 = "Cascades" if region == 181
replace region2 = "Centre" if region == 182
replace region2 = "Centre-Est" if region == 183
replace region2 = "Centre-Nord" if region == 184
replace region2 = "Centre-Ouest" if region == 185
replace region2 = "Centre-Sud" if region == 186
replace region2 = "Est" if region == 187
replace region2 = "Hauts-Bassins" if region == 188
replace region2 = "Nord" if region == 189
replace region2 = "Plateau Central" if region == 190
replace region2 = "Sahel" if region == 191
replace region2 = "Sud-Ouest" if region == 192
replace region2 = "S.Antão" if region == 220
replace region2 = "S.Vicente" if region == 221
replace region2 = "Santiago-Interior" if region == 222
replace region2 = "Santiago- Praia" if region == 223
replace region2 = "Fogo" if region == 224
replace region2 = "Western" if region == 260
replace region2 = "Central" if region == 261
replace region2 = "Greater Accra" if region == 262
replace region2 = "Volta" if region == 263
replace region2 = "Eastern" if region == 264
replace region2 = "Ashanti" if region == 265
replace region2 = "Brong Ahafo" if region == 266
replace region2 = "Northern" if region == 267
replace region2 = "Upper East" if region == 268
replace region2 = "Upper West" if region == 269
replace region2 = "Nairobi" if region == 300
replace region2 = "Central" if region == 301
replace region2 = "Eastern" if region == 302
replace region2 = "Rift Valley" if region == 303
replace region2 = "Nyanza" if region == 304
replace region2 = "Western" if region == 305
replace region2 = "North Eastern" if region == 306
replace region2 = "Coast" if region == 307
replace region2 = "Maseru" if region == 340
replace region2 = "Mafeteng" if region == 341
replace region2 = "Mohale’s Hoek" if region == 342
replace region2 = "Quthing" if region == 343
replace region2 = "Qacha’s Nek" if region == 344
replace region2 = "Mokhotlong" if region == 345
replace region2 = "Butha-Buthe" if region == 346
replace region2 = "Leribe" if region == 347
replace region2 = "Berea" if region == 348
replace region2 = "Thaba Tseka" if region == 349
replace region2 = "Bomi" if region == 380
replace region2 = "Bong" if region == 381
replace region2 = "Gbarpolu" if region == 382
replace region2 = "Grand Bassa" if region == 383
replace region2 = "Grand Cape Mount" if region == 384
replace region2 = "Grand Gedeh" if region == 385
replace region2 = "Grand Kru" if region == 386
replace region2 = "Lofa" if region == 387
replace region2 = "Margibi" if region == 388
replace region2 = "Maryland" if region == 389
replace region2 = "Montserrado" if region == 390
replace region2 = "Nimba" if region == 391
replace region2 = "River Cess" if region == 392
replace region2 = "River Gee" if region == 393
replace region2 = "Sinoe" if region == 394
replace region2 = "Alaotra Mangoro" if region == 420
replace region2 = "Amoron'i Mania" if region == 421
replace region2 = "Analamanga" if region == 422
replace region2 = "Analanjirofo" if region == 423
replace region2 = "Androy" if region == 424
replace region2 = "Anosy" if region == 425
replace region2 = "Atsimo Andrefana" if region == 426
replace region2 = "Atsimo Atsinanana" if region == 427
replace region2 = "Atsinanana" if region == 428
replace region2 = "Betsiboka" if region == 429
replace region2 = "Boeny" if region == 430
replace region2 = "Bongolava" if region == 431
replace region2 = "Diana" if region == 432
replace region2 = "Haute Matsiatra" if region == 433
replace region2 = "Ihorombe" if region == 434
replace region2 = "Itasy" if region == 435
replace region2 = "Melaky" if region == 436
replace region2 = "Menabe" if region == 437
replace region2 = "SAVA" if region == 438
replace region2 = "Sofia" if region == 439
replace region2 = "Vakinankaratra" if region == 440
replace region2 = "Vatovavy Fitovinany" if region == 441
replace region2 = "Central" if region == 460
replace region2 = "North" if region == 461
replace region2 = "South" if region == 462
replace region2 = "Kayes" if region == 501
replace region2 = "Koulikoro" if region == 502
replace region2 = "Sikasso" if region == 503
replace region2 = "Ségou" if region == 504
replace region2 = "Mopti" if region == 505
replace region2 = "Bamako" if region == 509
replace region2 = "Maputo Province" if region == 540
replace region2 = "Maputo City" if region == 541
replace region2 = "Gaza" if region == 542
replace region2 = "Inhambane" if region == 543
replace region2 = "Sofala" if region == 544
replace region2 = "Tete" if region == 545
replace region2 = "Manica" if region == 546
replace region2 = "Zambezia" if region == 547
replace region2 = "Nampula" if region == 548
replace region2 = "Cabo Delgado" if region == 549
replace region2 = "Niassa" if region == 550
replace region2 = "Caprivi" if region == 580
replace region2 = "Erongo" if region == 581
replace region2 = "Hardap" if region == 582
replace region2 = "Karas" if region == 583
replace region2 = "Kavango" if region == 584
replace region2 = "Khomas" if region == 585
replace region2 = "Kunene" if region == 586
replace region2 = "Ohangwena" if region == 587
replace region2 = "Omaheke" if region == 588
replace region2 = "Omusati" if region == 589
replace region2 = "Oshana" if region == 590
replace region2 = "Oshikoto" if region == 591
replace region2 = "Otjozondjupa" if region == 592
replace region2 = "Abia" if region == 620
replace region2 = "Adamawa" if region == 621
replace region2 = "Akwa-Ibom" if region == 622
replace region2 = "Anambra" if region == 623
replace region2 = "Bauchi" if region == 624
replace region2 = "Bayelsa" if region == 625
replace region2 = "Benue" if region == 626
replace region2 = "Borno" if region == 627
replace region2 = "Cross-River" if region == 628
replace region2 = "Delta" if region == 629
replace region2 = "Ebonyi" if region == 630
replace region2 = "Edo" if region == 631
replace region2 = "Ekiti" if region == 632
replace region2 = "Enugu" if region == 633
replace region2 = "FCT" if region == 634
replace region2 = "Gombe" if region == 635
replace region2 = "Imo" if region == 636
replace region2 = "Jigawa" if region == 637
replace region2 = "Kaduna" if region == 638
replace region2 = "Kano" if region == 639
replace region2 = "Katsina" if region == 640
replace region2 = "Kebbi" if region == 641
replace region2 = "Kogi" if region == 642
replace region2 = "Kwara" if region == 643
replace region2 = "Lagos" if region == 644
replace region2 = "Nasarawa" if region == 645
replace region2 = "Niger" if region == 646
replace region2 = "Ogun" if region == 647
replace region2 = "Ondo" if region == 648
replace region2 = "Osun" if region == 649
replace region2 = "Oyo" if region == 650
replace region2 = "Plateau" if region == 651
replace region2 = "Rivers" if region == 652
replace region2 = "Sokoto" if region == 653
replace region2 = "Taraba" if region == 654
replace region2 = "Yobe" if region == 655
replace region2 = "Zamfara" if region == 656
replace region2 = "Dakar" if region == 660
replace region2 = "Diourbel" if region == 661
replace region2 = "Fatick" if region == 662
replace region2 = "Kaffrine" if region == 663
replace region2 = "Kaolack" if region == 664
replace region2 = "Kédougou" if region == 665
replace region2 = "Kolda" if region == 666
replace region2 = "Louga" if region == 667
replace region2 = "Matam" if region == 668
replace region2 = "Saint-Louis" if region == 669
replace region2 = "Sédhiou" if region == 670
replace region2 = "Tambacounda" if region == 671
replace region2 = "Thies" if region == 672
replace region2 = "Ziguinchor" if region == 673
replace region2 = "Eastern Cape" if region == 700
replace region2 = "Free State" if region == 701
replace region2 = "Gauteng" if region == 702
replace region2 = "Kwazulu Natal" if region == 703
replace region2 = "Limpopo" if region == 704
replace region2 = "Mpumalanga" if region == 705
replace region2 = "North West" if region == 706
replace region2 = "Northern Cape" if region == 707
replace region2 = "Western Cape" if region == 708
replace region2 = "Dodoma" if region == 740
replace region2 = "Arusha" if region == 741
replace region2 = "Kilimanjaro" if region == 742
replace region2 = "Tanga" if region == 743
replace region2 = "Morogoro" if region == 744
replace region2 = "Pwani" if region == 745
replace region2 = "Dar es Salaam" if region == 746
replace region2 = "Lindi" if region == 747
replace region2 = "Mrwara" if region == 748
replace region2 = "Ruvuma" if region == 749
replace region2 = "Iringa" if region == 750
replace region2 = "Mbeya" if region == 751
replace region2 = "Singida" if region == 752
replace region2 = "Tabora" if region == 753
replace region2 = "Rukwa" if region == 754
replace region2 = "Kigoma" if region == 755
replace region2 = "Shinyanga" if region == 756
replace region2 = "Kagera" if region == 757
replace region2 = "Mwanza" if region == 758
replace region2 = "Mara" if region == 759
replace region2 = "Manyara" if region == 760
replace region2 = "Unguja Kaskazini" if region == 761
replace region2 = "Unfuja Kusini" if region == 762
replace region2 = "Mjini Magharibi" if region == 763
replace region2 = "Pemba Kaskazini" if region == 764
replace region2 = "Pemba Kusini" if region == 765
replace region2 = "Central" if region == 780
replace region2 = "West" if region == 781
replace region2 = "North" if region == 782
replace region2 = "East" if region == 783
replace region2 = "Kampala" if region == 784
replace region2 = "Lusaka" if region == 820
replace region2 = "Central" if region == 821
replace region2 = "Copperbelt" if region == 822
replace region2 = "Eastern" if region == 823
replace region2 = "Luapula" if region == 824
replace region2 = "Northern" if region == 825
replace region2 = "North-Western" if region == 826
replace region2 = "Southern" if region == 827
replace region2 = "Western" if region == 828
replace region2 = "Muchinga" if region == 829
replace region2 = "Harare" if region == 860
replace region2 = "Bulawayo" if region == 861
replace region2 = "Midlands" if region == 862
replace region2 = "Masvingo" if region == 863
replace region2 = "Mashonaland East" if region == 864
replace region2 = "Mashonaland West" if region == 865
replace region2 = "Mashonaland Central" if region == 866
replace region2 = "Matebeleland South" if region == 867
replace region2 = "Matebeleland North" if region == 868
replace region2 = "Manicaland" if region == 869
replace region2 = "Port Louis" if region == 900
replace region2 = "Pamplemousses" if region == 901
replace region2 = "Riviere du Rempart" if region == 902
replace region2 = "Flacq" if region == 903
replace region2 = "Grand Port" if region == 904
replace region2 = "Savanne" if region == 905
replace region2 = "Plaines Wilhems" if region == 906
replace region2 = "Moka" if region == 907
replace region2 = "Black River" if region == 908
replace region2 = "Rodrigues" if region == 909
replace region2 = "Western Area" if region == 930
replace region2 = "Southern Province" if region == 931
replace region2 = "Eastern Province" if region == 932
replace region2 = "Northern Province" if region == 933
replace region2 = "Agadez" if region == 1100
replace region2 = "Diffa" if region == 1101
replace region2 = "Dosso" if region == 1102
replace region2 = "Maradi" if region == 1103
replace region2 = "Tahoua" if region == 1104
replace region2 = "Tillabéri" if region == 1105
replace region2 = "Zinder" if region == 1106
replace region2 = "Niamey" if region == 1107
replace region2 = "Lomé commune" if region == 1140
replace region2 = "Maritime" if region == 1141
replace region2 = "Plateaux" if region == 1142
replace region2 = "Centrale" if region == 1143
replace region2 = "Kara" if region == 1144
replace region2 = "Savanes" if region == 1145
replace region2 = "Bubanza" if region == 1180
replace region2 = "Bujumbura" if region == 1181
replace region2 = "Bujumbura Mairie" if region == 1182
replace region2 = "Bururi" if region == 1183
replace region2 = "Cankuzo" if region == 1184
replace region2 = "Cibitoke" if region == 1185
replace region2 = "Gitega" if region == 1186
replace region2 = "Karusi" if region == 1187
replace region2 = "Kayanza" if region == 1188
replace region2 = "Kirundo" if region == 1189
replace region2 = "Makamba" if region == 1190
replace region2 = "Muramvya" if region == 1191
replace region2 = "Muyinga" if region == 1192
replace region2 = "Mwaro" if region == 1193
replace region2 = "Ngozi" if region == 1194
replace region2 = "Rutana" if region == 1195
replace region2 = "Ruyigi" if region == 1196
replace region2 = "Yaounde" if region == 1220
replace region2 = "Douala" if region == 1221
replace region2 = "Adamawa" if region == 1222
replace region2 = "Centre" if region == 1223
replace region2 = "East" if region == 1224
replace region2 = "Extreme-North" if region == 1225
replace region2 = "Littoral" if region == 1226
replace region2 = "North" if region == 1227
replace region2 = "North West" if region == 1228
replace region2 = "West" if region == 1229
replace region2 = "Sud" if region == 1230
replace region2 = "Sud-Ouest" if region == 1231
replace region2 = "Agneby" if region == 1260
replace region2 = "Bafing" if region == 1261
replace region2 = "Bas Sassandra" if region == 1262
replace region2 = "Denguele" if region == 1263
replace region2 = "Fromager" if region == 1264
replace region2 = "Haut Sassandra" if region == 1265
replace region2 = "Lacs" if region == 1266
replace region2 = "Lagunes" if region == 1267
replace region2 = "Marahoue" if region == 1268
replace region2 = "Montagnes" if region == 1269
replace region2 = "Moyen Cavally" if region == 1270
replace region2 = "Moyen Comoe" if region == 1271
replace region2 = "N’Zi Comoe" if region == 1272
replace region2 = "Savanes" if region == 1273
replace region2 = "Sud Bandama" if region == 1274
replace region2 = "Sud Comoe" if region == 1275
replace region2 = "Vallee du Bandama" if region == 1276
replace region2 = "Ville d’Abidjan" if region == 1277
replace region2 = "Worodougou" if region == 1278
replace region2 = "Zanzan" if region == 1279
replace region2 = "Conakry" if region == 1300
replace region2 = "Boké" if region == 1301
replace region2 = "Kindia" if region == 1302
replace region2 = "Mamou" if region == 1303
replace region2 = "Labé" if region == 1304
replace region2 = "Faranah" if region == 1305
replace region2 = "Kankan" if region == 1306
replace region2 = "N’Zérékoré" if region == 1307
replace region2 = "Adrar" if region == 1420
replace region2 = "Chlef" if region == 1421
replace region2 = "Laghouat" if region == 1422
replace region2 = "Oum el-Bouaghi" if region == 1423
replace region2 = "Batna" if region == 1424
replace region2 = "Bejaia" if region == 1425
replace region2 = "Biskra" if region == 1426
replace region2 = "Bechar" if region == 1427
replace region2 = "Blida" if region == 1428
replace region2 = "Bouria" if region == 1429
replace region2 = "Tamanghasset" if region == 1430
replace region2 = "Tebessa" if region == 1431
replace region2 = "Tlemcen" if region == 1432
replace region2 = "Tiaret" if region == 1433
replace region2 = "Tizi Ouzou" if region == 1434
replace region2 = "Algeris" if region == 1435
replace region2 = "Djelfa" if region == 1436
replace region2 = "Jijel" if region == 1437
replace region2 = "Setif" if region == 1438
replace region2 = "Saida" if region == 1439
replace region2 = "Skikda" if region == 1440
replace region2 = "Sidi Bel Abbes" if region == 1441
replace region2 = "Annaba" if region == 1442
replace region2 = "Guelma" if region == 1443
replace region2 = "Constantine" if region == 1444
replace region2 = "Medea" if region == 1445
replace region2 = "Mostaganem" if region == 1446
replace region2 = "M’Sila" if region == 1447
replace region2 = "Mascara" if region == 1448
replace region2 = "Ouargla" if region == 1449
replace region2 = "Oran" if region == 1450
replace region2 = "El Bayadh" if region == 1451
replace region2 = "Illizi" if region == 1452
replace region2 = "Bordj Bou Arreridj" if region == 1453
replace region2 = "Boumerdes" if region == 1454
replace region2 = "El Tarf" if region == 1455
replace region2 = "Cairo" if region == 1460
replace region2 = "Alexandria" if region == 1461
replace region2 = "Suez" if region == 1463
replace region2 = "Damietta" if region == 1464
replace region2 = "Dakahlia" if region == 1465
replace region2 = "Al-Sharqia" if region == 1466
replace region2 = "Qalyubia" if region == 1467
replace region2 = "Kafr el-Sheikh" if region == 1468
replace region2 = "Gharbia" if region == 1469
replace region2 = "Monufia" if region == 1470
replace region2 = "Beheira" if region == 1471
replace region2 = "Ismailia" if region == 1472
replace region2 = "Giza" if region == 1473
replace region2 = "Beni Suef" if region == 1474
replace region2 = "Faiyum" if region == 1475
replace region2 = "Minya" if region == 1476
replace region2 = "Asyut" if region == 1477
replace region2 = "Sohag" if region == 1478
replace region2 = "Qena" if region == 1479
replace region2 = "Luxor" if region == 1480
replace region2 = "Red Sea" if region == 1481
replace region2 = "Al Bahr al Ahmar" if region == 1482
replace region2 = "Chaouia-Ouardigha" if region == 1500
replace region2 = "Doukkala-Abda" if region == 1501
replace region2 = "Fes-Boulemane" if region == 1502
replace region2 = "Gharb-Charda-Beni Hssen" if region == 1503
replace region2 = "Grand Casablanca" if region == 1504
replace region2 = "Guelmim-Es Semara" if region == 1505
replace region2 = "Laayoune-Boujdour-Sakia El Hamra" if region == 1506
replace region2 = "Marrakech-Tensift-El Haouz" if region == 1507
replace region2 = "Meknes-Tafilalet" if region == 1508
replace region2 = "Oriental" if region == 1509
replace region2 = "Oued Ed-Dahab-Lagouira" if region == 1510
replace region2 = "Rabat-Sale-Zemmour-Zaer" if region == 1511
replace region2 = "Souss-Massa-Draa" if region == 1512
replace region2 = "Tadla-Azilal" if region == 1513
replace region2 = "Tangier-Tetouan" if region == 1514
replace region2 = "Taza-Al Hoceima-Taounate" if region == 1515
replace region2 = "North" if region == 1540
replace region2 = "Nahr ALnil" if region == 1541
replace region2 = "Red Sea" if region == 1542
replace region2 = "Kassala" if region == 1543
replace region2 = "Gadarif" if region == 1544
replace region2 = "Khartom" if region == 1545
replace region2 = "Gezira" if region == 1546
replace region2 = "White Nile" if region == 1547
replace region2 = "Sennar" if region == 1548
replace region2 = "Blue Nile" if region == 1549
replace region2 = "South Kordufan" if region == 1550
replace region2 = "North Kordufan" if region == 1551
replace region2 = "North Darfur" if region == 1552
replace region2 = "West Darfur" if region == 1553
replace region2 = "South Darfur" if region == 1554
replace region2 = "Tunis" if region == 1580
replace region2 = "Ariana" if region == 1581
replace region2 = "Ben Arous" if region == 1582
replace region2 = "Manouba" if region == 1583
replace region2 = "Nabeul" if region == 1584
replace region2 = "Zaghouan" if region == 1585
replace region2 = "Bizerte" if region == 1586
replace region2 = "Beja" if region == 1587
replace region2 = "Jendouba" if region == 1588
replace region2 = "Le Kef" if region == 1589
replace region2 = "Siliana" if region == 1590
replace region2 = "Sousse" if region == 1591
replace region2 = "Monastir" if region == 1592
replace region2 = "Mahdia" if region == 1593
replace region2 = "Sfax" if region == 1594
replace region2 = "Kairouan" if region == 1595
replace region2 = "Kasserine" if region == 1596
replace region2 = "Sidi Bouzid" if region == 1597
replace region2 = "Gabes" if region == 1598
replace region2 = "Mednine" if region == 1599
replace region2 = "Tataouine" if region == 1600
replace region2 = "Gafsa" if region == 1601
replace region2 = "Tozeur" if region == 1602
replace region2 = "Kebili" if region == 1603
replace region2 = "Hhohho" if region == 1620
replace region2 = "Manzini" if region == 1621
replace region2 = "Shiselweni" if region == 1622
replace region2 = "Lubombo" if region == 1623
}

save "afrobarometer\merged_r5_data_proc_georef.dta", replace

***************************************ROUND 6***************************************
import delimited "afrobarometer\afb_r6_extra_adm1_intersect.txt", clear delim(";")
save "afrobarometer\afb_r6_extra_adm1_intersect.dta", replace
import delimited "afrobarometer\afb_r6_adm1_intersect.txt", clear
quietly{
drop if country == 13 | country == 26 | country == 31 |  country == 34 |  country == 35 |  country == 36
append using "afrobarometer\afb_r6_extra_adm1_intersect.dta", force gen (extraset)
gen round6 = 1
gen year = substr(dateintr, -2, 2)
replace year = "2015" if year == "15"
replace year = "2014" if year == "14"
tostring id_0, gen (id_0str)
tostring id_1, gen (id_1str)
gen  ID_adm1 = "c" + id_0str + "r" + id_1str
gen ID_adm1_yr = ID_adm1 + " " + year
keep country region geoname__1 ea_svc_c ea_svc_d ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_fac_f ea_fac_g ea_sec_a ea_sec_b ea_sec_c ea_sec_d ea_sec_e ea_road_a ea_road_b q2 q4a q4b q5 q6 q7 q8a q8b q8c q8d q8e q27a q27d q27b q27e q28a q28b q28c q40 q41 q42a q42b q52a q52c q52b q52d q52e q52f q52g q52h q52i q52k q52j q52l q81a q81b q81c q88a q88b q89a q89b q90a q89e q90b q102  iso name_0 name_1 year ID_adm1 ID_adm1_yr q10a q11a q11b q13 q15a q15b q15c q16 q20b q22 q30 q24c q49 q51a
*ea_svc_a ea_svc_b
* Unify question names (create missings if this question was not asked in the respective round)


gen q10a_x = q10a
gen q11a_x = q11a
gen q11b_x = q11b
gen q13_x = q13
gen q15a_x = q15a
gen q15b_x = q15b
gen q15c_x = q15c
gen q16_x = q16
gen q20b_x = q20b
gen q22_x = q22
gen q30_x = q30
gen q24c_x = q24c
gen q49_x = q49
gen q51a_x = q51a



gen q7b = . 
gen round_afb = 6
* Unify names of geographic units over rounds
gen ISO = ""
replace ISO = "BWA" if country == 3
replace ISO = "GHA" if country == 11
replace ISO = "LSO" if country == 14
replace ISO = "MWI" if country == 17
replace ISO = "MLI" if country == 18
replace ISO = "NAM" if country == 22
replace ISO = "NGA" if country == 24
replace ISO = "ZAF" if country == 28
replace ISO = "TZA" if country == 31
replace ISO = "UGA" if country == 34
replace ISO = "ZMB" if country == 35
replace ISO = "ZWE" if country == 36
replace ISO = "CPV" if country == 7
replace ISO = "KEN" if country == 13
replace ISO = "MOZ" if country == 21
replace ISO = "SEN" if country == 26
replace ISO = "BEN" if country == 2
replace ISO = "MDG" if country == 16
replace ISO = "BFA" if country == 4
replace ISO = "LBR" if country == 15
replace ISO = "DZA" if country == 1
replace ISO = "BDI" if country == 5
replace ISO = "CMR" if country == 6
replace ISO = "CIV" if country == 8
replace ISO = "EGY" if country == 9
replace ISO = "GIN" if country == 12
replace ISO = "MUS" if country == 19
replace ISO = "MAR" if country == 20
replace ISO = "NER" if country == 23
replace ISO = "SLE" if country == 27
replace ISO = "SDN" if country == 29
replace ISO = "SWZ" if country == 30
replace ISO = "TGO" if country == 32
replace ISO = "TUN" if country == 33
replace ISO = "GAB" if country == 10
replace ISO = "STP" if country == 25
gen region2 = ""
replace region2 = "Alibori" if region == 100
replace region2 = "Atacora" if region == 101
replace region2 = "Atlantique" if region == 102
replace region2 = "Borgou" if region == 103
replace region2 = "Collines" if region == 104
replace region2 = "Couffo" if region == 105
replace region2 = "Donga" if region == 106
replace region2 = "Littoral" if region == 107
replace region2 = "Mono" if region == 108
replace region2 = "Ouémé" if region == 109
replace region2 = "Plateau" if region == 110
replace region2 = "Zou" if region == 111
replace region2 = "Barolong" if region == 140
replace region2 = "Central Bobonong" if region == 141
replace region2 = "Central Boteti" if region == 142
replace region2 = "Central Mahalapye" if region == 143
replace region2 = "Central Serowe/Palapye" if region == 144
replace region2 = "Central Tutume" if region == 145
replace region2 = "Chobe" if region == 146
replace region2 = "Francistown" if region == 147
replace region2 = "Gaborone" if region == 148
replace region2 = "Ghanzi" if region == 149
replace region2 = "Jwaneng" if region == 150
replace region2 = "Kgalagadi North" if region == 151
replace region2 = "Kgalagadi South" if region == 152
replace region2 = "Kgatleng" if region == 153
replace region2 = "Kweneng East" if region == 154
replace region2 = "Kweneng West" if region == 155
replace region2 = "Lobatse" if region == 156
replace region2 = "Ngamiland East" if region == 157
replace region2 = "Ngamiland West" if region == 158
replace region2 = "Ngwaketse" if region == 159
replace region2 = "Ngwaketse West" if region == 160
replace region2 = "North East" if region == 161
replace region2 = "Selibe Phikwe" if region == 162
replace region2 = "South East" if region == 163
replace region2 = "Boucle du Mouhoun" if region == 180
replace region2 = "Cascades" if region == 181
replace region2 = "Centre" if region == 182
replace region2 = "Centre Est" if region == 183
replace region2 = "Centre Nord" if region == 184
replace region2 = "Centre Ouest" if region == 185
replace region2 = "Centre Sud" if region == 186
replace region2 = "Est" if region == 187
replace region2 = "Hauts Bassins" if region == 188
replace region2 = "Nord" if region == 189
replace region2 = "Plateau Central" if region == 190
replace region2 = "Sahel" if region == 191
replace region2 = "Sud Ouest" if region == 192
replace region2 = "S. Antao" if region == 220
replace region2 = "S.Vincente" if region == 221
replace region2 = "Santiago-Interior" if region == 222
replace region2 = "Santiago-Praia" if region == 223
replace region2 = "Fogo" if region == 224
replace region2 = "Western" if region == 260
replace region2 = "Central" if region == 261
replace region2 = "Greater Accra" if region == 262
replace region2 = "Volta" if region == 263
replace region2 = "Eastern" if region == 264
replace region2 = "Ashanti" if region == 265
replace region2 = "Brong Ahafo" if region == 266
replace region2 = "Northern" if region == 267
replace region2 = "Upper East" if region == 268
replace region2 = "Upper West" if region == 269
replace region2 = "Nairobi" if region == 300
replace region2 = "Central" if region == 301
replace region2 = "Eastern" if region == 302
replace region2 = "Rift Valley" if region == 303
replace region2 = "Nyanza" if region == 304
replace region2 = "Western" if region == 305
replace region2 = "North Eastern" if region == 306
replace region2 = "Coast" if region == 307
replace region2 = "Maseru" if region == 340
replace region2 = "Mafeteng" if region == 341
replace region2 = "Mohale's Hoek" if region == 342
replace region2 = "Quthing" if region == 343
replace region2 = "Qacha's Nek" if region == 344
replace region2 = "Mokhotlong" if region == 345
replace region2 = "Buthe-Buthe" if region == 346
replace region2 = "Leribe" if region == 347
replace region2 = "Berea" if region == 348
replace region2 = "Thaba Tseka" if region == 349
replace region2 = "Bomi" if region == 380
replace region2 = "Bong" if region == 381
replace region2 = "Gbarpolu" if region == 382
replace region2 = "Grand Bassa" if region == 383
replace region2 = "Grand Cape Mount" if region == 384
replace region2 = "Grand Gedeh" if region == 385
replace region2 = "Grand Kru" if region == 386
replace region2 = "Lofa" if region == 387
replace region2 = "Margibi" if region == 388
replace region2 = "Maryland" if region == 389
replace region2 = "Montserrado" if region == 390
replace region2 = "Nimba" if region == 391
replace region2 = "Rivercess" if region == 392
replace region2 = "River Gee" if region == 393
replace region2 = "Sinoe" if region == 394
replace region2 = "Alaotra Mangoro" if region == 420
replace region2 = "Amoron'i Mania" if region == 421
replace region2 = "Analamanga" if region == 422
replace region2 = "Analanjirofo" if region == 423
replace region2 = "Androy" if region == 424
replace region2 = "Anosy" if region == 425
replace region2 = "Atsimo Andrefana" if region == 426
replace region2 = "Atsimo Atsinanana" if region == 427
replace region2 = "Atsinanana" if region == 428
replace region2 = "Betsiboka" if region == 429
replace region2 = "Boeny" if region == 430
replace region2 = "Bongolava" if region == 431
replace region2 = "Diana" if region == 432
replace region2 = "Haute Matsiatra" if region == 433
replace region2 = "Ihorombe" if region == 434
replace region2 = "Itasy" if region == 435
replace region2 = "Melaky" if region == 436
replace region2 = "Menabe" if region == 437
replace region2 = "SAVA" if region == 438
replace region2 = "Sofia" if region == 439
replace region2 = "Vakinankaratra" if region == 440
replace region2 = "Vatovavy Fitonany" if region == 441
replace region2 = "Central" if region == 460
replace region2 = "North" if region == 461
replace region2 = "South" if region == 462
replace region2 = "Kayes" if region == 501
replace region2 = "Koulikoro" if region == 502
replace region2 = "Sikasso" if region == 503
replace region2 = "Ségou" if region == 504
replace region2 = "Mopti" if region == 505
replace region2 = "Tombouctou" if region == 506
replace region2 = "Gao" if region == 507
replace region2 = "Kidal" if region == 508
replace region2 = "Bamako" if region == 509
replace region2 = "Maputo Province" if region == 540
replace region2 = "Maputo City" if region == 541
replace region2 = "Gaza" if region == 542
replace region2 = "Inhambane" if region == 543
replace region2 = "Sofala" if region == 544
replace region2 = "Tete" if region == 545
replace region2 = "Manica" if region == 546
replace region2 = "Zambézia" if region == 547
replace region2 = "Nampula" if region == 548
replace region2 = "Cabo Delgado" if region == 549
replace region2 = "Niassa" if region == 550
replace region2 = "Zambezi" if region == 580
replace region2 = "Erongo" if region == 581
replace region2 = "Hardap" if region == 582
replace region2 = "!Karas" if region == 583
replace region2 = "Kavango West" if region == 584
replace region2 = "Kavango East" if region == 585
replace region2 = "Khomas" if region == 586
replace region2 = "Kunene" if region == 587
replace region2 = "Ohangwena" if region == 588
replace region2 = "Omaheke" if region == 589
replace region2 = "Omusati" if region == 590
replace region2 = "Oshana" if region == 591
replace region2 = "Oshikoto" if region == 592
replace region2 = "Otjozondjupa" if region == 593
replace region2 = "Abia" if region == 620
replace region2 = "Akwa-Ibom" if region == 622
replace region2 = "Anambra" if region == 623
replace region2 = "Bauchi" if region == 624
replace region2 = "Bayelsa" if region == 625
replace region2 = "Benue" if region == 626
replace region2 = "Cross-River" if region == 628
replace region2 = "Delta" if region == 629
replace region2 = "Ebonyi" if region == 630
replace region2 = "Edo" if region == 631
replace region2 = "Ekiti" if region == 632
replace region2 = "Enugu" if region == 633
replace region2 = "FCT" if region == 634
replace region2 = "Gombe" if region == 635
replace region2 = "Imo" if region == 636
replace region2 = "Jigawa" if region == 637
replace region2 = "Kaduna" if region == 638
replace region2 = "Kano" if region == 639
replace region2 = "Katsina" if region == 640
replace region2 = "Kebbi" if region == 641
replace region2 = "Kogi" if region == 642
replace region2 = "Kwara" if region == 643
replace region2 = "Lagos" if region == 644
replace region2 = "Nasarawa" if region == 645
replace region2 = "Niger" if region == 646
replace region2 = "Ogun" if region == 647
replace region2 = "Ondo" if region == 648
replace region2 = "Osun" if region == 649
replace region2 = "Oyo" if region == 650
replace region2 = "Plateau" if region == 651
replace region2 = "Rivers" if region == 652
replace region2 = "Sokoto" if region == 653
replace region2 = "Taraba" if region == 654
replace region2 = "Zamfara" if region == 656
replace region2 = "Dakar" if region == 660
replace region2 = "Diourbel" if region == 661
replace region2 = "Fatick" if region == 662
replace region2 = "Kaffrine" if region == 663
replace region2 = "Kaolack" if region == 664
replace region2 = "Kédougou" if region == 665
replace region2 = "Kolda" if region == 666
replace region2 = "Louga" if region == 667
replace region2 = "Matam" if region == 668
replace region2 = "Saint-Louis" if region == 669
replace region2 = "Sédhiou" if region == 670
replace region2 = "Tambacounda" if region == 671
replace region2 = "Thies" if region == 672
replace region2 = "Ziguinchor" if region == 673
replace region2 = "Eastern Cape" if region == 700
replace region2 = "Free State" if region == 701
replace region2 = "Gauteng" if region == 702
replace region2 = "Kwazulu Natal" if region == 703
replace region2 = "Limpopo" if region == 704
replace region2 = "Mpumalanga" if region == 705
replace region2 = "North West" if region == 706
replace region2 = "Northern Cape" if region == 707
replace region2 = "Western Cape" if region == 708
replace region2 = "Dodoma" if region == 740
replace region2 = "Arusha" if region == 741
replace region2 = "Kilimanjaro" if region == 742
replace region2 = "Tanga" if region == 743
replace region2 = "Morogoro" if region == 744
replace region2 = "Pwani" if region == 745
replace region2 = "Dar es Salaam" if region == 746
replace region2 = "Lindi" if region == 747
replace region2 = "Mrwara" if region == 748
replace region2 = "Ruvuma" if region == 749
replace region2 = "Iringa" if region == 750
replace region2 = "Mbeya" if region == 751
replace region2 = "Singida" if region == 752
replace region2 = "Tabora" if region == 753
replace region2 = "Rukwa" if region == 754
replace region2 = "Kigoma" if region == 755
replace region2 = "Shinyanga" if region == 756
replace region2 = "Kagera" if region == 757
replace region2 = "Mwanza" if region == 758
replace region2 = "Mara" if region == 759
replace region2 = "Manyara" if region == 760
replace region2 = "Unguja Kaskazini" if region == 761
replace region2 = "Unfuja Kusini" if region == 762
replace region2 = "Mjini Magharibi" if region == 763
replace region2 = "Pemba Kaskazini" if region == 764
replace region2 = "Pemba Kusini" if region == 765
replace region2 = "Geita" if region == 766
replace region2 = "Katavi" if region == 767
replace region2 = "Njombe" if region == 768
replace region2 = "Simiyu" if region == 769
replace region2 = "Central" if region == 780
replace region2 = "West" if region == 781
replace region2 = "North" if region == 782
replace region2 = "East" if region == 783
replace region2 = "Kampala" if region == 784
replace region2 = "Lusaka" if region == 820
replace region2 = "Central" if region == 821
replace region2 = "Copperbelt" if region == 822
replace region2 = "Eastern" if region == 823
replace region2 = "Luapula" if region == 824
replace region2 = "Northern" if region == 825
replace region2 = "North-Western" if region == 826
replace region2 = "Southern" if region == 827
replace region2 = "Western" if region == 828
replace region2 = "Muchinga" if region == 829
replace region2 = "Harare" if region == 860
replace region2 = "Bulawayo" if region == 861
replace region2 = "Midlands" if region == 862
replace region2 = "Masvingo" if region == 863
replace region2 = "Mashonaland East" if region == 864
replace region2 = "Mashonaland West" if region == 865
replace region2 = "Mashonaland Central" if region == 866
replace region2 = "Matebeleland South" if region == 867
replace region2 = "Matebeleland North" if region == 868
replace region2 = "Manicaland" if region == 869
replace region2 = "Port Louis" if region == 900
replace region2 = "Pamplemousses" if region == 901
replace region2 = "Riviere du Rempart" if region == 902
replace region2 = "Flacq" if region == 903
replace region2 = "Grand Port" if region == 904
replace region2 = "Savanne" if region == 905
replace region2 = "Plaine Wilhems" if region == 906
replace region2 = "Moka" if region == 907
replace region2 = "Black River" if region == 908
replace region2 = "Rodrigues" if region == 909
replace region2 = "East" if region == 930
replace region2 = "North" if region == 931
replace region2 = "South" if region == 932
replace region2 = "Western" if region == 933
replace region2 = "Agadez" if region == 1100
replace region2 = "Dosso" if region == 1102
replace region2 = "Maradi" if region == 1103
replace region2 = "Tahoua" if region == 1104
replace region2 = "Tillabéri" if region == 1105
replace region2 = "Zinder" if region == 1106
replace region2 = "Niamey" if region == 1107
replace region2 = "Lome Commune" if region == 1140
replace region2 = "Maritime" if region == 1141
replace region2 = "Plateaux" if region == 1142
replace region2 = "Centrale" if region == 1143
replace region2 = "Kara" if region == 1144
replace region2 = "Savanes" if region == 1145
replace region2 = "Bubanza" if region == 1180
replace region2 = "Bujumbura" if region == 1181
replace region2 = "Bujumbura Marie" if region == 1182
replace region2 = "Bururi" if region == 1183
replace region2 = "Cankuza" if region == 1184
replace region2 = "Cibitoke" if region == 1185
replace region2 = "Gitega" if region == 1186
replace region2 = "Karusi" if region == 1187
replace region2 = "Kayanza" if region == 1188
replace region2 = "Kirundo" if region == 1189
replace region2 = "Makamba" if region == 1190
replace region2 = "Muramvya" if region == 1191
replace region2 = "Muyinga" if region == 1192
replace region2 = "Mwaro" if region == 1193
replace region2 = "Ngozi" if region == 1194
replace region2 = "Rutana" if region == 1195
replace region2 = "Ruyiga" if region == 1196
replace region2 = "Centre-Yaoundé" if region == 1220
replace region2 = "Littoral-Douala" if region == 1221
replace region2 = "Adamaoua" if region == 1222
replace region2 = "Centre" if region == 1223
replace region2 = "Est" if region == 1224
replace region2 = "Extrême-Nord" if region == 1225
replace region2 = "Littoral" if region == 1226
replace region2 = "Nord" if region == 1227
replace region2 = "Nord-Ouest" if region == 1228
replace region2 = "Ouest" if region == 1229
replace region2 = "Sud" if region == 1230
replace region2 = "Sud-Ouest" if region == 1231
replace region2 = "Abidjan" if region == 1260
replace region2 = "Agneby-Tiassa" if region == 1261
replace region2 = "Bafing" if region == 1262
replace region2 = "Bagoué" if region == 1263
replace region2 = "Bélier" if region == 1264
replace region2 = "Béré" if region == 1265
replace region2 = "Bounkani" if region == 1266
replace region2 = "Cavally" if region == 1267
replace region2 = "Gbékè" if region == 1269
replace region2 = "Gboklè" if region == 1270
replace region2 = "Gôh" if region == 1271
replace region2 = "Gontougo" if region == 1272
replace region2 = "Grands Ponts" if region == 1273
replace region2 = "Guémon" if region == 1274
replace region2 = "Hambol" if region == 1275
replace region2 = "Haut Sassandra" if region == 1276
replace region2 = "Iffou" if region == 1277
replace region2 = "Indenié-Djuablin" if region == 1278
replace region2 = "Kabadougou" if region == 1279
replace region2 = "La Mé" if region == 1280
replace region2 = "Lôh-Djiboua" if region == 1281
replace region2 = "Marahoué" if region == 1282
replace region2 = "Moronou" if region == 1283
replace region2 = "Nawa" if region == 1284
replace region2 = "N'Zi" if region == 1285
replace region2 = "Poro" if region == 1286
replace region2 = "San Pedro" if region == 1287
replace region2 = "Sud Comoé" if region == 1288
replace region2 = "Tchologo" if region == 1289
replace region2 = "Tonkpi" if region == 1290
replace region2 = "Worodougou" if region == 1291
replace region2 = "Conakry" if region == 1300
replace region2 = "Boké" if region == 1301
replace region2 = "Kindia" if region == 1302
replace region2 = "Mamou" if region == 1303
replace region2 = "Labé" if region == 1304
replace region2 = "Labé" if region == 1305
replace region2 = "Kankan" if region == 1306
replace region2 = "NZerekoré" if region == 1307
replace region2 = "North Eastern Region" if region == 1420
replace region2 = "North Middle Region" if region == 1421
replace region2 = "North Western Region" if region == 1422
replace region2 = "East Highlands" if region == 1423
replace region2 = "Middle Highlands" if region == 1424
replace region2 = "Western Highlands" if region == 1425
replace region2 = "South Eastern Region" if region == 1426
replace region2 = "South Western Region" if region == 1428
replace region2 = "Cairo" if region == 1460
replace region2 = "Alexandria" if region == 1461
replace region2 = "Port Said" if region == 1462
replace region2 = "Suez" if region == 1463
replace region2 = "Damietta" if region == 1464
replace region2 = "Dakahlia" if region == 1465
replace region2 = "Charqia" if region == 1466
replace region2 = "Qalyubia" if region == 1467
replace region2 = "Kafr el-Sheikh" if region == 1468
replace region2 = "Gharbia" if region == 1469
replace region2 = "Monufia" if region == 1470
replace region2 = "Beheira" if region == 1471
replace region2 = "Ismailia" if region == 1472
replace region2 = "Giza" if region == 1473
replace region2 = "Beni Souif" if region == 1474
replace region2 = "El Feyoum" if region == 1475
replace region2 = "El Menya" if region == 1476
replace region2 = "Assyout" if region == 1477
replace region2 = "Souhag" if region == 1478
replace region2 = "Qena" if region == 1479
replace region2 = "Luxor" if region == 1480
replace region2 = "Aswan" if region == 1481
replace region2 = "Red Sea" if region == 1482
replace region2 = "Matrouh" if region == 1483
replace region2 = "Oued Ed-Dahab-Lagouira" if region == 1500
replace region2 = "Laayoune-Boujdour-Sakia El Hamra" if region == 1501
replace region2 = "Guelmim-Es-Semara" if region == 1502
replace region2 = "Souss-Massa-Drâa" if region == 1503
replace region2 = "Gharb-Chrarda-Beni-Hssen" if region == 1504
replace region2 = "Chaouia-Ouardigha" if region == 1505
replace region2 = "Marrakech-Tensift-Al-Haouz" if region == 1506
replace region2 = "Oriental" if region == 1507
replace region2 = "Grand Casablanca" if region == 1508
replace region2 = "Rabat-Salé-Zemmour-Zaer" if region == 1509
replace region2 = "Doukkala-Abda" if region == 1510
replace region2 = "Tadla-Azilal" if region == 1511
replace region2 = "Meknès-Tafilalet" if region == 1512
replace region2 = "Fès-Bouleman" if region == 1513
replace region2 = "Taza-Al Hoceima-Taounate" if region == 1514
replace region2 = "Tanger-Tétouan" if region == 1515
replace region2 = "North" if region == 1540
replace region2 = "East" if region == 1541
replace region2 = "Khartoum" if region == 1542
replace region2 = "Central" if region == 1543
replace region2 = "Kurdufan" if region == 1544
replace region2 = "Darfur" if region == 1545
replace region2 = "Tunis" if region == 1580
replace region2 = "Ariana" if region == 1581
replace region2 = "Manouba" if region == 1582
replace region2 = "Ben Arous" if region == 1583
replace region2 = "Sfax" if region == 1584
replace region2 = "Sousse" if region == 1585
replace region2 = "Nabeul" if region == 1586
replace region2 = "Bizerte" if region == 1587
replace region2 = "Zaghouan" if region == 1588
replace region2 = "Sidi Bouzid" if region == 1589
replace region2 = "Le Kef" if region == 1590
replace region2 = "Kasserine" if region == 1591
replace region2 = "Jendouba" if region == 1592
replace region2 = "Bèja" if region == 1593
replace region2 = "Siliana" if region == 1594
replace region2 = "Kairouan" if region == 1595
replace region2 = "Monastir" if region == 1596
replace region2 = "Mahdia" if region == 1597
replace region2 = "Medenine" if region == 1598
replace region2 = "Tataouine" if region == 1599
replace region2 = "Gafsa" if region == 1600
replace region2 = "Gabès" if region == 1601
replace region2 = "Tozeur" if region == 1602
replace region2 = "Kébili" if region == 1603
replace region2 = "Hhohho" if region == 1620
replace region2 = "Manzini" if region == 1621
replace region2 = "Shiselweni" if region == 1622
replace region2 = "Lubombo" if region == 1623
replace region2 = "Sao Tome" if region == 1660
replace region2 = "Principe" if region == 1661
replace region2 = "Estuaire" if region == 1700
replace region2 = "Haut-Ogooué" if region == 1701
replace region2 = "Moyen-Ogooué" if region == 1702
replace region2 = "Ngounié" if region == 1703
replace region2 = "Nyanga" if region == 1704
replace region2 = "Ogooué-Ivindo" if region == 1705
replace region2 = "Ogooué-Lolo" if region == 1706
replace region2 = "Ogooué-Maritime" if region == 1707
replace region2 = "Woleu-Ntem" if region == 1708

gen country2 = ""
replace country2 = "Algeria" if country == 1
replace country2 = "Benin" if country == 2
replace country2 = "Botswana" if country == 3
replace country2 = "Burkina Faso" if country == 4
replace country2 = "Burundi" if country == 5
replace country2 = "Cameroon" if country == 6
replace country2 = "Cape Verde" if country == 7
replace country2 = "Cote d'Ivoire" if country == 8
replace country2 = "Egypt" if country == 9
replace country2 = "Gabon" if country == 10
replace country2 = "Ghana" if country == 11
replace country2 = "Guinea" if country == 12
replace country2 = "Kenya" if country == 13
replace country2 = "Lesotho" if country == 14
replace country2 = "Liberia" if country == 15
replace country2 = "Madagascar" if country == 16
replace country2 = "Malawi" if country == 17
replace country2 = "Mali" if country == 18
replace country2 = "Mauritius" if country == 19
replace country2 = "Morocco" if country == 20
replace country2 = "Mozambique" if country == 21
replace country2 = "Namibia" if country == 22
replace country2 = "Niger" if country == 23
replace country2 = "Nigeria" if country == 24
replace country2 = "São Tomé and Príncipe" if country == 25
replace country2 = "Senegal" if country == 26
replace country2 = "Sierra Leone" if country == 27
replace country2 = "South Africa" if country == 28
replace country2 = "Sudan" if country == 29
replace country2 = "Swaziland" if country == 30
replace country2 = "Tanzania" if country == 31
replace country2 = "Togo" if country == 32
replace country2 = "Tunisia" if country == 33
replace country2 = "Uganda" if country == 34
replace country2 = "Zambia" if country == 35
replace country2 = "Zimbabwe" if country == 36
}

save "afrobarometer\merged_r6_data_proc_georef.dta", replace

*You can skip the first part of this code segment by using the comand below
*use "afrobarometer\merged_r6_data_proc_georef.dta", clear
***************************************APPENDING***************************************
use "afrobarometer\merged_r6_data_proc_georef.dta", clear
quietly{
append using "afrobarometer\merged_r1_data_proc_georef.dta", force 
append using "afrobarometer\merged_r2_data_proc_georef.dta", force
append using "afrobarometer\merged_r3_data_proc_georef.dta", force
append using "afrobarometer\merged_r4_data_proc_georef.dta", force
append using "afrobarometer\merged_r5_data_proc_georef.dta", force
drop country region
ren country2 country
ren region2 region
ren year year_str
destring year_str, gen (year_num)
* AFB have an error to ensure annonymity, this leads to certain observations being wrong identified in the adm data 
* The following code should fix the error
/* MW 13.01.2019: This section is probably wrong, as it recodes all countries for the entire dataset
replace iso = "MAR" if iso == "ESH" 
replace name_0 = "Zambia" if iso == "AGO"
replace name_1 = "Western" if iso == "AGO"
replace iso = "ZMB" if iso == "AGO"
replace name_0 = "Tanzania" if region == "Ruvuma"  & iso == "MWI"
replace name_1 = "Ruvuma" if region == "Ruvuma"  & iso == "MWI"
replace iso = "TZA" if region == "Ruvuma"  & iso == "MWI"
replace name_1 = "Ruvuma" if region == "Ruvuma"  & name_1 == "Pwani"
replace name_0 = "Guinea" if iso == "MLI" & ISO == "GIN"
replace name_1 = "Kankan" if iso == "MLI" & region == "Kankan"
replace iso = "GIN" if iso == "MLI" & region == "Kankan"
replace name_0 = "Swaziland"  if region == "Lubombo" 
replace name_1 = "Lubombo"  if region == "Lubombo" 
replace iso =  "SWZ"  if region == "Lubombo" 
replace name_0 = "Niger"  if region == "Tahoua"
replace name_1 = "Tahoua"  if region == "Tahoua"
replace iso =  "NER"  if region == "Tahoua"
replace name_1 = "Ngounie" if name_1 == "NgouniÃ©"
replace name_1 = "Extreme-Nord" if name_1 == "ExtrÃªme-Nord"
replace name_1 = "Labe" if name_1 == "LabÃ©"
replace name_1 = "Karas" if name_1 == "!Karas"
replace name_1 = "Kavango" if name_1 == "Kavango East"
replace name_1 = "Kavango" if name_1 == "kavango"
replace iso = "TGO" if ISO == "BEN"
replace iso = "NAM" if ISO == "BWA"
replace iso = "TCD" if ISO == "CMR"
replace iso = "COG" if ISO == "GAB"
replace iso = "TGO" if ISO == "GHA"
replace iso = "MLI" if ISO == "GIN"
replace iso = "UGA" if ISO == "KEN"
replace iso = "ZAF" if ISO == "LSO"
replace iso = "MRT" if ISO == "MLI"
replace iso = "ZMB" if ISO == "NAM"
replace iso = "ZAF" if ISO == "NAM"
replace iso = "MWI" if ISO == "TZA"
replace iso = "GHA" if ISO == "TGO"
replace iso = "RWA" if ISO == "UGA"
replace iso = "MOZ" if ISO == "ZMB"
replace iso = "TZA" if ISO == "ZMB"
replace iso = "MOZ" if ISO == "ZWE"
replace name_0 = "Togo" if country == "Benin"
replace name_0 = "Namibia" if country == "Botswana"
replace name_0 = "Chad" if country == "Cameroon"
replace name_0 = "Republic of Congo" if country == "Gabon"
replace name_0 = "Togo" if country == "Ghana"
replace name_0 = "Guinea" if country == "Guinea"
replace name_0 = "Uganda" if country == "Kenya"
replace name_0 = "South Africa" if country == "Lesotho"
replace name_0 = "Mauritania" if country == "Mali"
replace name_0 = "Zambia" if country == "Namibia"
replace name_0 = "South Africa" if country == "Namibia"
replace name_0 = "Malawi" if country == "Tanzania"
replace name_0 = "Ghana" if country == "Togo"
replace name_0 = "Rwanda" if country == "Uganda"
replace name_0 = "Mozambique" if country == "Zambia"
replace name_0 = "Tanzania" if country == "Zambia"
replace name_0 = "Mozambique" if country == "Zimbabwe"
replace name_1 = "Maritime" if region == "Kouffo"
replace name_1 = "Zambezi" if region == "North West"
replace name_1 = "Hadjer-Lamis" if region == "Extreme-Nord"
replace name_1 = "Niari" if region == "Ngounié"
replace name_1 = "Plateaux" if region == "Volta"
replace name_1 = "Maritime" if region == "Volta"
replace name_1 = "Kayes" if region == "Labé"
replace name_1 = "Kapchorwa" if region == "Western"
replace name_1 = "Free State" if region == "Mafeteng"
replace name_1 = "Free State" if region == "Maseru"
replace name_1 = "Guidimaka" if region == "Kayes"
replace name_1 = "Hodh el Gharbi" if region == "Kayes"
replace name_1 = "Western" if region == "Zambezi"
replace name_1 = "Northern Cape" if region == "Karas"
replace name_1 = "Western" if region == "Kavango"
replace name_1 = "Karonga" if region == "Iringa"
replace name_1 = "Upper East" if region == "Savanes"
replace name_1 = "Iburasirazuba" if region == "Ntungamo"
replace name_1 = "Tete" if region == "Eastern" & ISO == "ZMB"
replace name_1 = "Mbeya" if region == "Muchinga"
replace name_1 = "Tete" if region == "Mashonaland Central"
replace name_1 = "Tete" if region == "Mashonaland East"
*/
}


*generating year and event dummies
quietly{
forvalues jahr = 1999(1)2015 {
gen jahr`jahr' = 0
replace jahr`jahr' = 1 if year_num == `jahr'
label var jahr`jahr' "Dumm for "
}
}


***************************************RECODING AND COLLAPSING VARIABLES**************************
quietly {
replace ea_fac_a = . if ea_fac_a == 9
replace ea_fac_b = . if ea_fac_b == 9
replace ea_fac_c = . if ea_fac_c == 9
replace ea_fac_d = . if ea_fac_d == 9
replace ea_fac_e = . if ea_fac_e == 9
replace ea_fac_f = . if ea_fac_f == 9
replace ea_fac_g = . if ea_fac_g == -1
replace ea_fac_g = . if ea_fac_g == 9
replace ea_sec_a = . if ea_sec_a == 9
replace ea_sec_b = . if ea_sec_b == 9
replace ea_sec_c = . if ea_sec_c == 9
replace ea_sec_d = . if ea_sec_d == 9
replace ea_sec_e = . if ea_sec_e == 9
replace q4a = . if q4a == -1
replace q4a = . if q4a == 6
replace q4a = . if q4a == 0
replace q4a = . if q4a == 9
replace q4a = . if q4a == 99
replace q4b = . if q4b == -1
replace q4b = . if q4b == 0
replace q4b = . if q4b == 9
replace q4b = . if q4b == 98
replace q4b = . if q4b == 99
replace q5 = . if q5 == -1
replace q5 = . if q5 == 0
replace q5 = . if q5 == 9
replace q5 = . if q5 == 98
replace q5 = . if q5 == 99
replace q52a = . if q52a == -1
replace q52a = . if q52a == 9
replace q52a = . if q52a == 98
replace q52a = . if q52a == 99
replace q52a = . if q52a == 997
replace q52b = . if q52b == -1
replace q52b = . if q52b == 9
replace q52b = . if q52b == 98
replace q52b = . if q52b == 99
replace q52b = . if q52b == 997
replace q52c = . if q52c == -1
replace q52c = . if q52c == 9
replace q52c = . if q52c == 98
replace q52c = . if q52c == 99
replace q52c = . if q52c == 997
replace q52d = . if q52d == -1
replace q52d = . if q52d == 9
replace q52d = . if q52d == 98
replace q52d = . if q52d == 99
replace q52e = . if q52e == -1
replace q52e = . if q52e == 9
replace q52e = . if q52e == 98
replace q52e = . if q52e == 99
replace q52f = . if q52f == -1
replace q52f = . if q52f == 9
replace q52f = . if q52f == 98
replace q52f = . if q52f == 99
replace q52f = . if q52f == 997
replace q52g = . if q52g == -1
replace q52g = . if q52g == 9
replace q52g = . if q52g == 98
replace q52g = . if q52g == 99
replace q52g = . if q52g == 997
replace q52h = . if q52h == -1
replace q52h = . if q52h == 9
replace q52h = . if q52h == 98
replace q52h = . if q52h == 99
replace q52h = . if q52h == 997
replace q52i = . if q52i == -1
replace q52i = . if q52i == 9
replace q52i = . if q52i == 98
replace q52i = . if q52i == 99
replace q52i = . if q52i == 997
replace q52j = . if q52j == -1
replace q52j = . if q52j == 9
replace q52j = . if q52j == 98
replace q52j = . if q52j == 99
replace q52j = . if q52j == 997
replace q52k = . if q52k == -1
replace q52k = . if q52k == 9
replace q52k = . if q52k == 98
replace q52k = . if q52k == 99
replace q52k = . if q52k == 997
replace q52l = . if q52l == -1
replace q52l = . if q52l == 9
replace q6 = . if q6 == -1
replace q6 = . if q6 == 0
replace q6 = . if q6 == 9
replace q6 = . if q6 == 98
replace q6 = . if q6 == 99
replace q7 = . if q7 == -1
replace q7 = . if q7 == 0
replace q7 = . if q7 == 9
replace q7 = . if q7 == 98
replace q7 = . if q7 == 99
replace q7b = . if q7b == -1
replace q7b = . if q7b == 0
replace q7b = . if q7b == 9
replace q7b = . if q7b == 98
replace q7b = . if q7b == 99
replace q88a = . if q88a == -1
replace q88a = . if q88a == 4
replace q88a = . if q88a == 5
replace q88a = . if q88a == 7
replace q88a = . if q88a == 9
replace q88a = . if q88a == 98
replace q88a = . if q88a == 99
replace q88a = . if q88a == 997
replace q88b = . if q88b == -1
replace q88b = . if q88b == 0
replace q88b = . if q88b == 7
replace q88b = . if q88b == 9
replace q88b = . if q88b == 98
replace q88b = . if q88b == 99
replace q88b = . if q88b == 997
replace q89a = . if q89a == 0
replace q89a = . if q89a == -1
replace q89a = . if q89a == 7
replace q89a = . if q89a == 9
replace q89a = . if q89a == 98
replace q89a = . if q89a == 99
replace q89a = . if q89a == 997
replace q89b = . if q89b == -1
replace q89b = . if q89b == 0
replace q89b = . if q89b == 7
replace q89b = . if q89b == 9
replace q89b = . if q89b == 98
replace q89b = . if q89b == 99
replace q89b = . if q89b == 997
replace q89e = . if q89e == -1
replace q89e = . if q89e == 0
replace q89e = . if q89e == 7
replace q89e = . if q89e == 9
replace q89e = . if q89e == 98
replace q89e = . if q89e == 99
replace q89e = . if q89e == 997
replace q8a = . if q8a == -1
replace q8a = . if q8a == 5
replace q8a = . if q8a == 7
replace q8a = . if q8a == 9
replace q8a = . if q8a == 98
replace q8a = . if q8a == 99
replace q8b = . if q8b == -1
replace q8b = . if q8b == 5
replace q8b = . if q8b == 7
replace q8b = . if q8b == 9
replace q8b = . if q8b == 98
replace q8b = . if q8b == 99
replace q8c = . if q8c == -1
replace q8c = . if q8c == 5
replace q8c = . if q8c == 7
replace q8c = . if q8c == 9
replace q8c = . if q8c == 98
replace q8c = . if q8c == 99
replace q8d = . if q8d == -1
replace q8d = . if q8d == 5
replace q8d = . if q8d == 7
replace q8d = . if q8d == 9
replace q8d = . if q8d == 98
replace q8d = . if q8d == 99
replace q8e = . if q8e == -1
replace q8e = . if q8e == 9
replace q8f = . if q8f == -1
replace q8f = . if q8f == 5
replace q8f = . if q8f == 7
replace q8f = . if q8f == 9
replace q8f = . if q8f == 98
replace q8f = . if q8f == 99
replace q27a = . if q27a == -1
replace q27a = . if q27a == 9
replace q27a = . if q27a == 98
replace q27b = . if q27b == -1
replace q27b = . if q27b == 9
replace q27d = . if q27d == -1
replace q27d = . if q27d == 9
replace q27e = . if q27e == -1
replace q27e = . if q27e == 9
replace q28a = . if q28a == -1
replace q28a = . if q28a == 0
replace q28a = . if q28a == 6
replace q28a = . if q28a == 9
replace q28a = . if q28a == 98
replace q28a = . if q28a == 99
replace q28b = . if q28b == -1
replace q28b = . if q28b == 0
replace q28b = . if q28b == 6
replace q28b = . if q28b == 9
replace q28b = . if q28b == 98
replace q28b = . if q28b == 99
replace q28c = . if q28c == -1
replace q28c = . if q28c == 0
replace q28c = . if q28c == 6
replace q28c = . if q28c == 9
replace q28c = . if q28c == 98
replace q28c = . if q28c == 99
replace q40 = . if q40 == -1
replace q40 = . if q40 == 0
replace q40 = . if q40 == 8
replace q40 = . if q40 == 9
replace q40 = . if q40 == 97
replace q40 = . if q40 == 98
replace q40 = . if q40 == 99
replace q40 = . if q40 == 998
replace q41 = . if q41 == -1
replace q41 = . if q41 == 5
replace q41 = . if q41 == 8
replace q41 = . if q41 == 9
replace q41 = . if q41 == 97
replace q41 = . if q41 == 98
replace q41 = . if q41 == 99
replace q42a = . if q42a == -1
replace q42a = . if q42a == 0
replace q42a = . if q42a == 9
replace q42b = . if q42b == -1
replace q42b = . if q42b == 0
replace q42b = . if q42b == 9
replace q42b = . if q42b == 98
replace q81a = . if q81a == -1
replace q81a = . if q81a == 9
replace q81a = . if q81a == 99
replace q81b = . if q81b == -1
replace q81b = . if q81b == 0
replace q81b = . if q81b == 9
replace q81b = . if q81b == 99
replace q81c = . if q81c == -1
replace q81c = . if q81c == 9
replace q81c = . if q81c == 99
replace q90a = . if q90a == -1
replace q90a = . if q90a == 2
replace q90a = . if q90a == 3
replace q90a = . if q90a == 7
replace q90a = . if q90a == 8
replace q90a = . if q90a == 9
replace q90a = . if q90a == 98
replace q90a = . if q90a == 99
replace q90a = . if q90a == 997
replace q90b = . if q90b == 9997
replace q90b = . if q90b == 9998
replace q90b = . if q90b == 9999
replace q90b = . if q90b == -1

replace q10a_x = . if q10a_x == -1
replace q10a_x = . if q10a_x == 9
replace q11a_x = . if q11a_x  == -1
replace q11a_x = . if q11a_x  == 9
replace q11b_x = . if q11b_x  == -1
replace q11b_x = . if q11b_x  == 9
replace q11b_x = . if q11b_x  ==  98
replace q11b_x = . if q11b_x  == 997
replace q13_x = . if q13_x == -1
replace q13_x = . if q13_x == 9
replace q13_x = . if q13_x == 998
replace q15a_x = . if q15a_x == -1
replace q15a_x = . if q15a_x == 9
replace q15b_x = . if q15b_x == -1
replace q15b_x = . if q15b_x == 9
replace q15b_x = . if q15b_x == 98
replace q15c_x = . if q15c_x == -1
replace q15c_x = . if q15c_x == 9 
replace q16_x = . if q16_x == -1
replace q16_x = . if q16_x == 5
replace q16_x = . if q16_x == 9
replace q20b_x = . if q20b_x == -1
replace q20b_x = . if q20b_x == 9
replace q20b_x = . if q20b_x == 99
replace q22_x = . if q22_x == -1
replace q22_x = . if q22_x == 8
replace q22_x = . if q22_x == 9
replace q22_x = . if q22_x == 98
replace q22_x = . if q22_x == 99
replace q30_x = . if q30_x == -1
replace q30_x = . if q30_x ==9
replace q30_x = . if q30_x ==97
replace q30_x = . if q30_x ==98
replace q30_x = . if q30_x == 99
replace q24c_x = . if q24c_x == -1
replace q24c_x = . if q24c_x == 5
replace q24c_x = . if q24c_x == 9
replace q24c_x = . if q24c_x == 98
replace q24c_x = . if q24c_x == 99
replace q49_x = . if q49_x == -1
replace q49_x = . if q49_x == 9
replace q51a_x = . if q51a_x == -1
replace q51a_x = . if q51a_x == 9
}

//q27a q27b q27d q27e q28a q28b q28c q40 q41 q42a q42b q52l q81a q81b q81c q90a q90b q102

*collapse (first) country region ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_fac_f ea_fac_g ea_sec_a ea_sec_b ea_sec_c ea_sec_d ea_sec_e ea_road_a ea_road_b id_0 name_0 id_1 name_1 ID_adm1_yr round* ISO iso jahr* year_num year_str (mean) q2 q4a q4b q5 q6 q7 q8a q8b q8c q8d q8e q27a q27b q27d q27e q28a q28b q28c q40 q41 q42a q42b q52a q52b q52c q52d q52e q52f q52g q52h q52i q52j q52k q52l q81a q81b q81c q88a q88b q89a q89b q89e q90a q90b q102 q7b q8f, by(ID_adm1)
*ea_svc_a ea_svc_c ea_svc_d 
egen Number = rowtotal(jahr*)


/* MW: 13.01.2019: There are some error in Afrobarometer when trying to match the points spatially to administrative regions.
Thus, we drop 349 obs out of 188240*/
drop if iso!=ISO
collapse (first) country region  id_0 name_0 id_1 name_1 ID_adm1 round* ISO iso jahr* year_num year_str ///
 (mean) ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_fac_f ea_fac_g ea_sec_a ea_sec_b ea_sec_c ea_sec_d ea_sec_e ea_road_a ea_road_b q2 q4a q4b q5 q6 /// 
 q7 q8a q8b q8c q8d q8e q27a q27b q27d q27e q28a q28b q28c q40 q41 q42a q42b q52a q52b q52c q52d q52e q52f q52g q52h q52i q52j q52k q52l q81a q81b q81c q88a /// 
 q88b q89a q89b q89e q90a q90b q102 q7b q8f q10a_x q11a_x q11b_x q13_x q15a_x q15b_x q15c_x q16_x q20b_x q22_x q30_x q24c_x q49_x q51a_x, by(ID_adm1_yr)

***************************************Labeling***************************************
quietly{
/*label var ea_svc_a "Services: … Electricity grid that most houses could access?"
label var ea_svc_b "Services: … Piped water system that most houses could access?"
label var ea_svc_c "Services: … Sewage system that most houses could access?"
label var ea_svc_d "Services: … Cell phone service?" */
label var ea_fac_a "Facilities: Post-Office ?"
label var ea_fac_b "Facilities: School?"
label var ea_fac_c "Facilities: Police station?"
label var ea_fac_d "Facilities: Health clinic?"
label var ea_fac_e "Facilities: Market stalls (selling groceries and/or clothing)?"
label var ea_fac_f "Facilities: Bank ?"
label var ea_fac_g "Facilities: Is there any kind of paid transport, such as a bus, taxi, moped, or other form, available on a daily basis?"
label var ea_sec_a "Security: Any policemen or police vehicles?"
label var ea_sec_b "Security: Any soldiers or army vehicles?"
label var ea_sec_c "Security: Any roadblocks set up by police or army?"
label var ea_sec_d "Security: Any customs checkpoints?"
label var ea_sec_e "Security: Any roadblocks or booms set up by the local community?"
label var ea_road_a "Road quality: Was the road at the start point in the PSU/EA paved/ tarred/ concrete?"
label var ea_road_b "Road quality: Was the road to this EA impassible at any point, for example, due to absence of a bridge or a collapsed bridge, broken down vehicle, fallen tree or water-logged section??"
label var q2 "Language: Which language is your home language?"
label var q4a "View of Economy: The present economic condition of this country ?"
label var q4b "View of Economy: Your own present living conditions?"
label var q5 "View of Economy: In general, how do you rate your living conditions compared to those of other [ENTER NATIONALITY]? "
label var q6 "View of Economy: Looking back, how do you rate economic conditions in this country compared to twelve months ago?"
label var q7 "View of Economy: Country’s economic condition in 12 months time"
label var q7b "View of Economy: Your living conditions in 12 months time"
label var q8a "Hardship: Gone without food"
label var q8b "Hardship: Gone without water"
label var q8c "Hardship: Gone without healthcare"
label var q8d "Hardship: Gone without electricity"
label var q8e "Hardship: Gone without income"
label var q27a "Civil disobediance: Join others to request government action"
label var q27b "Civil disobediance: Contact media"
label var q27d "Civil disobediance: Refuse to pay a tax or fee to government "
label var q27e "Civil disobediance: Attend a demonstration or protest march"
label var q28a "Governance: Reject one-party rule"
label var q28b "Governace: Reject military rule"
label var q28c "Governace: Reject one-man rule"
label var q40 "Democracy: Extent of democracy"
label var q41 "Democracy: Satisfaction with democracy"
label var q42a "Rule of Law: Courts make binding decisions"
label var q42b "Rule of Law: People must obey the law"
label var q52a "Trust Government: The President?"
label var q52b "Trust Government: Parliament?"
label var q52c "Trust Government: The Electoral Commission"
label var q52d "Trust Government: tax department"
label var q52e "Trust Government: your elected local government council"
label var q52f "Trust Government: the ruling party"
label var q52g "Trust Government: opposition political parties"
label var q52h "Trust Government: police"
label var q52i "Trust Government: army"
label var q52j "Trust Government: courts of law"
label var q52k "Trust Government: traditional leaders"
label var q52l "Trust Government: religous leaders"
label var q81a "China's Influnce: China's influence on economy"
label var q81b "China's Influnce: China's influence: positive or negative"
label var q81c "China's Influnce: Positive image of China"
label var q88a "Ethnic group treated unfairly : How often, if ever, are ___________s [R’s Ethnic Group] treated unfairly by the government?"
label var q88b "Ethnic or national identity: Let us suppose that you had to choose between being a [ENTER NATIONALITY] and being a ________ [R’s Ethnic Group].    Which of the following best expresses your feelings? "
label var q89a "Neighbours: People of different religion."
label var q89b "Neighbours: People from other ethnic groups."
label var q89e "Neighbours: immigrants and foreign workers"
label var q90a "Political Affiliation: Do you feel close to any particular political party?"
label var q90b "Political Affiliation: Which party is that?"
label var q102 "Ethnic Group: Ethnic Group"

label var q10a_x "Over the past year, how often, if ever, have you or anyone in your family: Felt unsafe walking in your neighborhood?"
label var q11a_x "During the past year, have you or anyone in your family: Had something stolen from your house? "
label var q11b_x "During the past year, have you or anyone in your family: Been physically attacked?"
label var q13_x "How interested would you say you are in public affairs?"
label var q15a_x "In this country, how free are you: To say what you think?"
label var q15b_x "In this country, how free are you: To join any political organization you want?"
label var q15c_x "In this country, how free are you: To choose who to vote for without feeling pressured."
label var q16_x "Statement 1: Government should be able to ban any organization that goes against its policies. Statement 2: We should be able to join any organization, whether or not the government approves of it."
label var q20b_x "Got together with others to raise an issue?"
label var q22_x "On the whole, how would you rate the freeness and fairness of the last national election, held in ____"
label var q30_x " Statement 1: Democracy is preferable to any other kind of government. Statement 2: In some circumstances, a non-democratic government can be preferable. Statement 3: For someone like me, it doesn’t matter what kind of government we have."
label var q24c_x "During the past year, how often have you contacted any of the following persons about some important problem or to give them your views: An official of a government agency?"
label var q49_x "During election campaigns: fear becoming a victim of political intimidation or violence?"
label var q51a_x "How often do people have to be careful of what they say about politics?"


 
label var region "Region name not georeferenced"
label var country "Counrty name not georeferenced"
*label var country "DON'T USE! Country ID from afb not georeferenced"
*label var region "DON'T USE! Region ID from afb not georeferenced"
label var ISO "3-letter COuntry ISO code Not Georeferenced"
label var iso "3-letter COuntry ISO code Georeferenced"
label var ID_adm1 "Unique Identifier of adm1"
label var ID_adm1_yr "Unique Identifier of adm1-year"
label var name_0 "Country name from gadm Georeferenced"
label var name_1 "Region name from gadm Georeferenced"
label var round_afb "Afrobarometer Round from which data originated"
label var year_num "Year in which survey was taken (numerical)"
label var year_str "Year in which survey was taken (string)"
*label var dateintr "Date on which interview was conducted"
}







save "afrobarometer\afb_all_rounds_georef.dta", replace

use "afrobarometer\afb_all_rounds_georef.dta", clear
***************************************OLS***************************************
use  "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\Data\Merge\IDA_Aid_GED_ADM1.dta", clear

// drop small economies and islands
drop if d_smallcountry ==1

* Generate continental identifiers
egen continent_num=group(unregion2)


* Generate Trend Variables:
sort ID_adm1_num transaction_year
bysort ID_adm1_num: egen mean_temp=mean(temp)
egen trend=group(transaction_year)
gen trend_sq=trend*trend
gen trend_cub=trend*trend*trend

 
* Aid data
gen lnaid=ln(WBAID_ADM1_LOC+0.01)
label var lnaid  "Log of population weighted World Bank Aid per capita +0.01"
gen lnaid_c=ln(CAID_ADM1_LOC+0.01)
label var lnaid_c  "Log of population weighted Chinese Aid per capita +0.01"



*
* Conflict Data
* Rename SCAD variables for consistency with other labeling
renvars D_pro_gov_violence D_demo D_riot D_strike D_total_violence D_total_rds / D_pro_gov_v_adm1 D_demo_adm1 D_riot_adm1 D_strike_adm1 D_tot_v_adm1 D_tot_rds_adm1
* Rescale Dummies for easier interpretation
foreach var in D_pro_gov_v_adm1 D_demo_adm1 D_riot_adm1 D_strike_adm1 D_tot_v_adm1 D_tot_rds_adm1 {
replace `var'=`var'*100
}
gen lnbrd_adm1=ln(brd_adm1+0.01)
* Genreate new intensity thresholds
gen inten1_adm1=0
replace inten1_adm1=100 if brd_adm1>=5

* Prepare Ending and Onset Indicators
sort ID_adm1_num transaction_year
xtset ID_adm1_num transaction_year
gen d1inten1_adm1=d1.inten1_adm1
* Onset and Ending on regional level
gen onset_adm1=0
replace onset_adm1=100 if d1inten1_adm1==100

gen ending_adm1=0
replace ending_adm1=100 if d1inten1_adm1==-100

* Onset and Ending on country level
bysort state transaction_year: egen conflict_country=max(inten1_adm1)
sort ID_adm1_num transaction_year
gen d1conflict_country=d1.conflict_country
gen onset_country=0
replace onset_country=100 if d1conflict_country==100
gen ending_country=0
replace ending_country=100 if d1conflict_country==-100

* Generate Latitude and Longitude for time-interaction
gen lon=abs(xcoord)
gen lat=abs(ycoord)

*** Prepare IV - WB Aid***
gen received=0
replace received=1 if WBAID_ADM1_LOC!=0 & WBAID_ADM1_LOC!=. 
gen eligible=1
sort ID_adm1_num transaction_year
bysort ID_adm1_num: gen eligible_sum=sum(eligible)
bysort ID_adm1_num: gen received_sum=sum(received)
bysort ID_adm1_num: egen eligible_tot=total(eligible)
bysort ID_adm1_num: egen received_tot=total(received)
gen prob_cum=received_sum/eligible_sum
gen prob_cons=received_tot/eligible_tot

drop eligible* received*

*** Prepare IV - Chinese Aid***
gen received=0
replace received=1 if CAID_ADM1_LOC!=0 & CAID_ADM1_LOC!=. & transaction_year>=2000 & transaction_year<=2012
gen eligible=1 if transaction_year>=2000 & transaction_year<=2012
sort ID_adm1_num transaction_year
bysort ID_adm1_num: gen eligible_sum=sum(eligible) if transaction_year>=2000 & transaction_year<=2012
bysort ID_adm1_num: gen received_sum=sum(received) if transaction_year>=2000 & transaction_year<=2012
bysort ID_adm1_num: egen eligible_tot=total(eligible) if transaction_year>=2000 & transaction_year<=2012
bysort ID_adm1_num: egen received_tot=total(received) if transaction_year>=2000 & transaction_year<=2012
gen prob_cum_c=received_sum/eligible_sum if transaction_year>=2000 & transaction_year<=2012
gen prob_cons_c=received_tot/eligible_tot if transaction_year>=2000 & transaction_year<=2012

drop eligible* received*

* Detrend Chinese steel production:
* Lennart 23.03.2018: Why should we add 0.01 as steel production is always positive & larger zero between 2000-2012
*create ln of steel variable
gen steel_prod_ln=ln(steel_prod+0.01)

capture gen constant=1
reg steel_prod_ln trend constant, nocons
gen steel_prod_ln_detrend = steel_prod_ln-_b[trend]*trend-_b[constant]

* Prepare IBRD variable
gen temp_IBRD = IBRD_ADM1_LOC
replace temp_IBRD=0 if IBRD_ADM1_LOC<=0
gen ln_IBRD_ADM1_LOC=ln(temp_IBRD+0.01)



* Create IDA_position fluctuation cleaned from general trends
reg IDA_position trend
predict IDA_position_cleaned, resid


* Create running average of IDA & IBRD position (e.g., for aid in t IDA_position in t determines the first half of the transaction_year and aid in t+1 determines the second half of the transaction_year)
sort ID_adm1_num transaction_year
gen IDA_position_run=(IDA_position+f1.IDA_position)/2
gen IBRD_EtL_Ratio_run=(IBRD_EtL_Ratio+f1.IBRD_EtL_Ratio)/2
* For out of sample values refer to IDA_IBRD values from Brad Parks (see Data/Aid/IDA_IBRD_Liquidity.xlsx)
replace IBRD_EtL_Ratio_run=(0.268+0.2698)/2 if transaction_year==2012
replace IDA_position_run=(0.788+0.807)/2 if transaction_year==2012
replace IBRD_EtL_Ratio_run=(0.217+0.2142)/2 if transaction_year==1995
replace IDA_position_run=(1.106+0.981)/2 if transaction_year==1995



* Detrend IDA Position
capture gen constant=1
reg IDA_position_run trend constant, nocons
gen IDA_position_run_detrend = IDA_position_run-_b[trend]*trend-_b[constant]


* Set Chinese aid to missing for non-African states and year<2000 & years>2012
replace lnaid_c=. if unregion2!="Africa" | transaction_year<2000 | transaction_year>2012

* Create Indicator for only one group
gen one_group=0
replace one_group=1 if ngroup_adm1_10km==1
* Power Status Indicators:
/*
gen share_powerfull=(st_DOMINANT +st_JUNIORPARTNER +st_MONOPOLY+  st_SENIORPARTNER)/ngroup_adm1_10km
gen share_powerless=(st_DISCRIMINATED +st_IRRELEVANT +st_POWERLESS +st_SELFEXCLUSION)/ngroup_adm1_10km
gen share_discriminated=st_DISCRIMINATED/ngroup_adm1_10km
gen share_selfexcluded=st_SELFEXCLUSION/ngroup_adm1_10km

* Create Indicator if there is one group that is part of the coalition
gen group_coalition=0
replace group_coalition=1 if (st_DISCRIMINATED!=0 | st_IRRELEVANT!=0 | st_POWERLESS!=0 | st_SELFEXCLUSION!=0 | st_STATECOLLAPSE!=0 ) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 | st_JUNIORPARTNER>0)
replace group_coalition=2 if (st_DISCRIMINATED==0 & st_IRRELEVANT==0 & st_POWERLESS==0 & st_SELFEXCLUSION==0 & st_STATECOLLAPSE==0 ) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 | st_JUNIORPARTNER>0)
label var group_coalition "Categorical variable: (0) all powerless; (1) some with power; (2) exclusively with power"
* Create Indicator if there is one group that is part of the coalition & also regions which are exclusively part of the coaliton
gen group_coalition_s=0
replace group_coalition_s=1 if (st_DISCRIMINATED!=0 | st_IRRELEVANT!=0 | st_POWERLESS!=0 | st_SELFEXCLUSION!=0 | st_STATECOLLAPSE!=0 ) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 | st_JUNIORPARTNER>0)
replace group_coalition_s=2 if (st_DISCRIMINATED==0 & st_IRRELEVANT==0 & st_POWERLESS==0 & st_SELFEXCLUSION==0 & st_STATECOLLAPSE==0 ) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 | st_JUNIORPARTNER>0)
replace group_coalition_s=3 if (st_DISCRIMINATED==0 & st_IRRELEVANT==0 & st_POWERLESS==0 & st_SELFEXCLUSION==0 & st_STATECOLLAPSE==0 ) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 | st_JUNIORPARTNER>0) & ngroup_adm1_10km==1
label var group_coalition_s "Categorical variable: (0) all powerless; (1) some with power; (2) exclusively with power; (3) one group exclusively with power"

* Create Indicator if there is dominant/monopoly group or senior partner in region
gen group_dms=0
replace group_dms=1 if (st_DISCRIMINATED!=0 | st_IRRELEVANT!=0 | st_POWERLESS!=0 | st_SELFEXCLUSION!=0 | st_STATECOLLAPSE!=0 | st_JUNIORPARTNER!=0) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 )
replace group_dms=2 if (st_DISCRIMINATED==0 & st_IRRELEVANT==0 & st_POWERLESS==0 & st_SELFEXCLUSION==0 & st_STATECOLLAPSE==0 & st_JUNIORPARTNER==0) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 )
*replace group_dms=3 if (st_DISCRIMINATED==0 & st_IRRELEVANT==0 & st_POWERLESS==0 & st_SELFEXCLUSION==0 & st_STATECOLLAPSE==0 & st_JUNIORPARTNER==0) & (st_DOMINANT>0 | st_MONOPOLY>0 | st_SENIORPARTNER>0 ) & ngroup_adm1_10km==1
label var group_dms "Categorical variable: (0) all powerless; (1) some dominant, monopoly, senior; (2) exclusively dominant, monopoly, senior"
* Create Indicator if there is one group in power and one without power
gen group_inex=0
replace group_inex=1 if share_powerfull>0 & share_powerless>0
label var group_inex "Dummy if there are both groups that are included and excluded from power"
* Create Indicator if there are junior partners together with more powerful groups (senior partner, monopoly, dominant) in one region
gen group_jothers=0
replace group_jothers=1 if st_JUNIORPARTNER>0 & (st_SENIORPARTNER>0 | st_DOMINANT>0 | st_MONOPOLY>0)
label var group_jothers "Junior partner along with one monopoly or dominant group within a region"

*/
**Merge Afrobarometer data***********************************************

keep if unregion2=="Africa"
tostring year, gen (yearstr)
ren year year_num
ren yearstr year
gen ID_adm1_yr = ID_adm1 + " " + year
ren _merge _mergeold
merge 1:m ID_adm1_yr using "afrobarometer\afb_all_rounds_georef.dta", keep(1 3)

save "afrobarometer\afb_all_rounds_georef_conflict_merged.dta", replace
ssssssss
/*By using the flie below you can jump to this part of the script
use "afrobarometer\afb_all_rounds_georef_conflict_merged.dta", clear*/

*xtset ID_adm1_num year_num
global control2 droughtend_spi droughtstart_spi temp prec_gpcc c.ttime_mean#i.transaction_year  c.landarea#i.transaction_year  c.dist_capital#i.transaction_year c.mean_temp#i.transaction_year c.elevation_std#i.transaction_year c.Dborder#i.transaction_year c.Driver#i.transaction_year c.Dlake#i.transaction_year c.Docean#i.transaction_year c.lat#i.transaction_year c.lon#i.transaction_year
* Set endogenous controls with one further lage for Chinese aid as we use commitments with two lags
global control3 L3.drug_y L3.gem_y  L3.isum_pop_ADM2 L3.goldsurface_y L3.diamprim_y L3.diamsec_y L3.petroleum_y L3.excluded  L3.ln_IBRD_ADM2_LOC L3.lights_sum

*Afrobarometer Globals -  Creating Globals for the Afrobarometer Categories
quietly{
global afb_fac ea_fac_a ea_fac_b ea_fac_c ea_fac_d ea_fac_e ea_fac_f ea_fac_g
global afb_sec ea_sec_a ea_sec_b ea_sec_c ea_sec_d ea_sec_e 
global afb_road ea_road_a ea_road_b
global afb_economy q4a q4b q5 q6 q7 q7b
global afb_hardship q8a q8b q8c q8d q8e q8f
global afb_governance q28a q28b q28c
global afb_democracy q40 q41
global afb_rule_law q42a q42b
global afb_trust_gov q52a q52b q52c q52d q52e q52f q52g q52h q52i q52j q52k q52l
}

sort ID_adm1_num transaction_year
local TreatVar l1.lnaid l2.lnaid_c
foreach t in $TreatVar {
clear esto
foreach i in $afb_fac {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_sec {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}

foreach i in $afb_road {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_economy {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_governance {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_democracy {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_rule_law {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region tren
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}
foreach i in $afb_trust_gov {
//6. continental trend + country FE + transaction_year FE + Region FE + country trends + D1 and D3  + quadratic country trend + region trend
reghdfe `i' l1.lnaid $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin =_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l1.lnaid]/_se[l1.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe `i' `t' $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[`t']*100
local pvalue=(2 * ttail(e(df_r), abs(_b[`t']/_se[`t'])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
}



//table for all regressions
* Prepare estimates for -estout-
	estfe _all, labels( ///
	state "Country FE" ///
	transaction_year "Transaction_year FE" ///
	ID_adm2_num "Region FE" ///
	continent_num#transaction_year "Time Trends" ///
	ID_adm2_num#c.trend "Regional Trend" ///
	ID_adm2_num#c.trend_sq "Quadratic Regional Trend" ///
	state#transaction_year "Country-Transaction_year FE")
	return list
	di ""`r(indicate_fe)'""
* prepare macro for control variables
global exo_t_c  *.transaction_year#c.landarea *.transaction_year#c.dist_capital *.transaction_year#c.mean_temp *.transaction_year#c.ttime_mean *.transaction_year#c.elevation_std *.transaction_year#c.Dborder *.transaction_year#c.Driver *.transaction_year#c.Dlake *.transaction_year#c.Docean
* Add later "*.transaction_year#c.lat *.transaction_year#c.lon" above
global end_t_c L2.drug_y L2.gem_y L2.isum_pop_ADM2 L2.goldsurface_y L2.diamprim_y L2.diamsec_y L2.petroleum_y L2.excluded L2.ln_IBRD_ADM2_LOC L2.lights_sum
global end_t_c3 L3.drug_y L3.gem_y L3.isum_pop_ADM2 L3.goldsurface_y L3.diamprim_y L3.diamsec_y L3.petroleum_y L3.excluded L3.ln_IBRD_ADM2_LOC L3.lights_sum
if "`t'"=="l1.lnaid"{
esttab _all using "$tables\cluster2multi_wb_`g'", replace booktabs fragment keep(L.lnaid) coeflabels(L.lnaid "\[ln(World\,Bank\,Aid\,\textsubscript{t-1})\]") ///
se star(* 0.10 ** 0.05 *** 0.01) b(%9.4f) compress  plain nomtitles parentheses prehead("Intensity 1 - World Bank") postfoot(\hline)
}
if "`t'"=="l2.lnaid_c"{
esttab _all using "$tables\cluster2multi_china_`g'", replace booktabs fragment keep(L2.lnaid_c ) coeflabels(L2.lnaid_c "\[ln(China\,Aid\,\textsubscript{t-2})\]") ///
se star(* 0.10 ** 0.05 *** 0.01) b(%9.4f) compress  plain nomtitles parentheses prehead("Intensity 1 - China") postfoot(\hline)
}

}

/*
reghdfe inten1_adm1 l2.lnaid_c $control2 , cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend)
local margin=_b[l1.lnaid_c]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l2.lnaid_c]/_se[l2.lnaid_c])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo
//8. continental trend + country FE + transaction_year Fe + Region FE + D1 and D3 + continental trend + quadratic country trend + region trend + Country-Transaction_year FE
reghdfe inten1_adm1 l1.lnaid  $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[l1.lnaid]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l2.lnaid]/_se[l2.lnaid])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo

reghdfe inten1_adm1 l2.lnaid_c $control2, cluster(i.state#i.transaction_year i.ID_adm1_num) absorb(i.continent_num#i.transaction_year i.state i.transaction_year i.ID_adm1_num i.state#c.trend i.state#c.trend_sq i.ID_adm1_num#c.trend i.state#i.transaction_year)
local margin=_b[l2.lnaid_c]*100
local pvalue=(2 * ttail(e(df_r), abs(_b[l2.lnaid_c]/_se[l2.lnaid_c])))
local pvalue=`pvalue'
display "pvalue=" `pvalue'
estadd scalar pvalue_aid = `pvalue'
eststo

esttab _all using "Y:\entwicklungspolitik\Hiwi\A_Kai\Michele\Geocoded_Conflict_data\stata\tables\xyz.txt", replace booktabs fragment keep(q4a q4b q5 q6 q7 q7b) coeflabels (q4a "present economic condition" q4b "own present living condition" q5 "living conditions compared to others" q6 "country compared to twelve months ago?" q7 "own 12 months time" q7b " living conditions in 12 months time") ///
se star(* 0.10 ** 0.05 *** 0.01) b(%9.4f) compress  plain nomtitles parentheses prehead("Intensity 1 - World Bank") postfoot(\hline)
*/
